Keep value of first row and replace the rest with zero

[cchetrusca]

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

[LordNeilLord · Accepted 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

[ephij]

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.