SOLVED
Keep value of first row and replace the rest with zero
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
cchetrusca
5 - Atom
‎12-06-2019
02:26 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I have the first table now and need to obtain the second one.
Table 1
Name | ID | Revenue |
Mark | 1 | 20 |
Mark | 1 | 25 |
Mark | 1 | 30 |
John | 2 | 12 |
John | 2 | 10 |
Ben | 3 | 15 |
Ben | 3 | 24 |
Ben | 3 | 20 |
Table 2
Name | ID | Revenue |
Mark | 1 | 20 |
Mark | 0 | 25 |
Mark | 0 | 30 |
John | 2 | 12 |
John | 0 | 10 |
Ben | 3 | 15 |
Ben | 0 | 24 |
Ben | 0 | 20 |
I need to keep the ID value only of first row per each unique name.
Please advise,
Thanks,
Constantin
Solved! Go to Solution.
Labels:
- Labels:
- Workflow
2 REPLIES 2
LordNeilLord
15 - Aurora
‎12-06-2019
02:29 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @cchetrusca
Using the multi-row formula, grouping on name, you can update the ID column with something like this:
if [Name]=[Row-1:Name] Then 0 Else [ID] endif
ephij
9 - Comet
‎12-06-2019
03:38 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Could also sort the table, placing the record you want as your ID field first, then a Unique tool to pull first record out and then add a formula tool to set the id to 0 for the "duplicates" and union the two (uniques and formula output) back together.