How to identify unique values in the column

Vandana

Hello all,

I want to label the unique entries in the column as unique and repeated entries as duplicate by using beast mode.

Could anyone please help me on how to do this..

Attaching screenshot of the expected result table :

Thank you

GrantSmith

Hi @Vandana

You can use some window functions to get the row number SUM(SUM(1)) and then compare that number to 1 since you're wanting the first instance to be unique and all others to be duplicate. You'll need to talk with your CSM to get the window functions in beast modes enabled if you don't already have it.

CASE WHEN SUM(SUM(1)) OVER (PARTITION BY `Email`) = 1 THEN 'Unique' ELSE Duplicate END

Vandana

Hi @GrantSmith ,

Thank you for the solution, I have requested to get the window functions in beast mode. Unfortunately, it might take sometime :(

Could you please let me know if you have any other solution in mind..

GrantSmith

You could utilize a Magic ETL dataflow and utilize the Rank and Window function to calculate the row number based on your partition and then use a formula tile to conditionally set the value of each record.

CASE WHEN `Row Number` = 1 THEN 'Unique' ELSE 'Duplicate' END

Row Number being the new field you calculated with the Rank & Window tile.

Vandana

Okay, will try this..

Thank you for the quick help, Very much appreciated :)

Vandana

Hi @GrantSmith :

There are null values as well in the column values, Think cause of that it is failing..

I retried by mapping nulls to NA, still no luck..

CASE WHEN SUM(SUM(1)) OVER (PARTITION BY `Email`) = 1 THEN 'Unique' ELSE 'Duplicate' END

Could you please suggest how to tackle same

Vandana

GrantSmith

Did you get window functions turned on in your instance?

Vandana

Yes, @Grant ..