community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

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

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

@Justyna,

 

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:

 

IIF(
     [SOS_input] > [Row-1:SOS_output], 
     [SOS_input], 
     Max([First_SOS_input],[SOS_input],[Row-1:SOS_output])
)

Capture.PNG

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
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.

 

Meteor

@MarqueeCrew

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 ....so it does not solving the problem.  

 

In data you would have only column SOS_input. 

 

Thank you anyway!

Cheers:)

Meteor

Hi:)

I found solution...is 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:) 

Cheers!

 

Labels