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

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
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

 

 

 

 

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