Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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

7 - Meteor

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.
7 - Meteor

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.


7 - Meteor


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!


7 - Meteor


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:)