Hi all,
I have a data series where I have several products (A...C) and data over multiple periods. The data shows the uptake of values at a certain moment in time (period) and has leading 0 values. After the start, values may drop to 0 (or even below).
I am looking for an EasyMorph process to remove or skip the leading 0 until the start of the respective product's uptake but not clear for all 0 values. The intention is to create a "relative" period dimension where the period #1 is the first period of the uptake.
For selectively removing the leading 0s 'Filter' or 'CleanUp' does not work.
Attached an sample Excel table where column D (Relative Period) would be the column I am looking to achieve.
assign a sequential row number to each input row (i.e. each row in the input dataset), split by each Product. You can use the 'Enumerate rows' action.
in an intermediate step (i.e. a separate derived table), remove the rows where "Value = 0". Then compute the minimal input row number for each Product. This gives, for each Product, the first row where the values are not equal to zero.
merge this intermediate table back into the input table. Based on both row numbers (namely the input row number, and the first non-zero row number), one can compute the relative Period column as follows:
if [RowNo] < [FirstNonZeroRowNo]
then empty()
else [RowNo] - [FirstNonZeroRowNo] + 1
