Multi row to apply values if zero "0"
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Preparation
- Tips and Tricks
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is awesome, thanks a million! I completely forgot about grouping them. The formula worked marvelously.
Cheers!