Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Replace 0 with the highest not null value for Id and dates series for this Id


Hi All:)

I am new to Alteryx. Playing with it last 4 weeks.
Trying to solve the  issue explained below. Can’t find anything useful when browsing.
I have store ID and date sorted by id and date.

Then my SOS_Input  looks like the one below (print screen attached).
What I need is to do is: IF [My_ID] == [Row-1:My_ID]   take the first non zero value from SOS_input  and fill the entire series for this store and all date with this value until new different non zero value will be in SOS_input  - desired output my SOS_output.

Here in print screen (below) is the same value couple of times 0.375 till 05/09/2016 when it is 0.386, but it will be easier to see in test file with data attached. 

There are 2 sheets in file Smaller with only two different ID and bigger with multiple ids.

Hope my explanation make sense.
I was trying to use multi row action, not sure is this is good direction.

Thanks in advance!data_test.PNG





Alteryx Certified Partner
Alteryx Certified Partner



I solved the challenge by filtering out the 0 values and then sampling the first record for each store.  I then joined the data back together and used a multi-row formula.  That formula looks for the highest value of (Input, Previous output and the first Input) and sets that as the output value.  Here's the formula:


     [SOS_input] > [Row-1:SOS_output], 





Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Thank you very much! :)

I will take a look and check this one.

I was trying to create new table with dates and first value for the date and then replace nulls with those values but it didn't work.




Hi Mark:)

Actually your workflow  it using the SOS_output field in calculation - which is actually only to show how the desired output should look like it does not solving the problem.  


In data you would have only column SOS_input. 


Thank you anyway!




I found not the most elegant one but it does the work.

If someone will have similar problem may make it better then this one and share:)