We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Keep value of first row and replace the rest with zero

cchetrusca
5 - Atom

Hi, 

 

I have the first table now and need to obtain the second one. 

 

 Table 1

Name IDRevenue
Mark120
Mark125
Mark130
John212
John210
Ben315
Ben324
Ben320

 

Table 2 

Name IDRevenue
Mark120
Mark025
Mark030
John212
John010
Ben315
Ben024
Ben020

 

I need to keep the ID value only of first row per each unique name. 

 

Please advise, 

 

Thanks, 

Constantin

2 REPLIES 2
LordNeilLord
15 - Aurora

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

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.

Labels