Multi row to apply values if zero "0"

[juan1]

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. 

ID	Charge
15870018633147	0
15870018633147	0
15870018633147	0
15870018633147	0
15870018633147	3816
15870018633147	3816
15870018633147	3816
15870018633147	3816
15870018633147	3816
15870018633147	3816

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

[PhilipL · Accepted solution]

@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 · Accepted solution]

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]

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]

@DanM 

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

Cheers!