We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Flagging all duplicates

kas
8 - Asteroid

Hello,

 

I need to include in my output a flag of all the duplicate records of one column, so the data in the other columns can be reviewed.

I know I can use the Multi row Formula tool with the formula below, but that will mark one of the duplicate records as 1 and the other as 0 - I need both to be marked as 1.

 

IF [Value = [Row-1:Value] THEN 1 ELSE 0

 

Example Current:

Value                 Name          DOB

123456789        Smith          1/1/1980

123456789        Smith          1/12/1980

568790561        Jones          3/8/1949 

 

Desired output:

Value                 Name        DOB              Flag

123456789        Smith        1/1/1980         1

123456789        Smith        1/12/1980       1

568790561        Jones        3/8/1949        0

 

How can I use this tool to achieve this?

 

Thank you,

Kim

6 REPLIES 6
abacon
12 - Quasar

@kas Change you formula to this - IF [Value] = [Row-1:Value] THEN 1 ELSEIF [Value] = [Row+1:Value] THEN 1 ELSE 0. It is telling the records to look below AND above for a duplicate.

kas
8 - Asteroid

@abacon 

 

It says "malformed statement" - this is what I put.

 

IF [SSN] = [Row-1:SSN] THEN 1 ELSEIF [SSN] = [Row+1:SSN] THEN 1 ELSE 0

 

I totally see your logic in the statement but can't see any error in my version.

 

Thanks again,

Kim

abacon
12 - Quasar

Throw an ENDIF at the end, I didn't complete the statement, that's my bad.

davidskaife
14 - Magnetar

Hi @kas 

 

You need to finish the formula off with 'ENDIF'

kas
8 - Asteroid

@abacon 

 

OH good grief...I know that!

Apparently my brain is completely shot today...

 

Worked like a charm :) 

Thanks so much!

abacon
12 - Quasar

All good, I feel that haha.

Labels
Top Solution Authors