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