We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Multi row to apply values if zero "0"

juan1
7 - Meteor

Dear all,

 

I have the following data set and I am trying to apply the values for the same ID if the cell is 0. This happens across all my dataset but just wanted to see if this case works so that then I can apply it to all. 

IDCharge
158700186331470
158700186331470
158700186331470
158700186331470
158700186331473816
158700186331473816
158700186331473816
158700186331473816
158700186331473816
158700186331473816

 

Pretty much I want the same charge across all rows, as these have more columns that make them not "unique". 

 

I did the following multi-row formula but am getting something wrong:

 

 

 

IF [Row-1:ID] = [ID] AND [Charge] = 0
THEN [Row-1:Charge]
ELSE [Charge]
ENDIF

 

 

 

 

Maybe there is a simpler way to do this, but I have the tendency to overcomplicate things.

 

Thank you all in advance for your time and assistance!

 

Edit: I cannot use:

 

[ROW+1:ID] 

 

as it would grab the charge for a different ID.

 

Best,

Juan1

4 REPLIES 4
PhilipL
Alteryx
Alteryx

@juan1 

 

Hi Juan,

 

It looks like some of your records have nothing but 0 charges for some of your IDs, so not sure what you want to do there. For those that have transferable values I went with a different approach - join your 0 charge records back to the non-0 records and pass the non-0 charge value. I did it with a Formula copy of the fields but a Select with a field selection/rename could work too.

 

Check out the attached example.

 

Thanks,

Phil

DanM
Alteryx Community Team
Alteryx Community Team

You'll want to Sort the data by ID Ascending and Charge Descending. 

 

Then in a multi-row formula field, Choose the Charge field and group by the ID. then use the expression If [Charge]=0 then [Row-1:Charge] else [Charge] endif

 

You only have to IDs in the data that have a 0 and another #. All the rest are greater than one or have 0 for the all the values.

juan1
7 - Meteor

Thank you! I intuitively did the same thing, it was killing me trying to get the multi-row to work that I thought about separating them and then joining them back together.

 

 

juan1
7 - Meteor

@DanM 

 

This is awesome, thanks a million! I completely forgot about grouping them. The formula worked marvelously. 

 

Cheers!

Labels