How to identify unique values in the column
Answers
-
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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..
0 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Okay, will try this..
Thank you for the quick help, Very much appreciated :)
0 -
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
0 -
0
-
Did you get window functions turned on in your instance?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 763 Beast Mode
- 65 App Studio
- 42 Variables
- 709 Automate
- 184 Apps
- 460 APIs & Domo Developer
- 54 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 134 Manage
- 131 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive