Removing duplicates
Hi, I need help coming up with logic for this problem. I have a dataset that looks like this:
ID Source 1 A 1 A 1 B 1 B 2 A 3 B
I want to only remove rows from source A IF that same row is also in source B. Otherwise I want to keep rows unique to source A and unique to source B. The output should look like this:
ID Source 1 B 1 B 2 A 3 B
Thank you
Best Answer
-
@jrtomici Yes I re-read your question and realized that my original solution would not work. What I would recommend is self joining the data on itself for every record in A. This way you can compare each row of data in A with every row in B and look for matches. This will temporarily explode your data, but you can then filter it down before the output.
If I solved your problem, please select "yes" above
0
Answers
-
Using the remove duplicates tile in magic etl you can choose what fields make a row unique. So in your case you would select just ID and Name to search for the duplicates.
EDIT: I re-read your question and the above solution would not get you what you are looking for. You would need to self join the data with itself so that the data can be compared to itself row by row. This will temporarili yexplode your data but hen you can filter it down before the final output.
If I solved your problem, please select "yes" above
1 -
@jrtomici If you are using MagicETL, try using a Group By tile to condense your table. Set ID and Name as your columns to group by, and add a formula grouping for MAX(Source). Note that this will keep the source that is last alphabetically since your example includes "A" and "B" as your values, but you may need to use MIN instead if the value you want to keep is first alphabetically.
0 -
@ColemenWilson Ugh I apologize — what makes this complicated is that the Name field is not unique. So there is no unique identifier for each row across both sources.
0 -
@jrtomici Yes I re-read your question and realized that my original solution would not work. What I would recommend is self joining the data on itself for every record in A. This way you can compare each row of data in A with every row in B and look for matches. This will temporarily explode your data, but you can then filter it down before the output.
If I solved your problem, please select "yes" above
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive