Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Justyna
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

 

 

 

 

4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Justyna
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.

 

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

Justyna
7 - 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