How is remove duplicate actually work?
Currently I work using remove duplicate in magic ETL, to remove some data with same id number for example.
The thing is, I am not really sure which data that domo removed when I am using remove duplicate. Here are what I need to reconfirm :
1. When performing remove duplicates, which data that will be removed? Is it will choose last row data to remain in dataset?
2. If so, if I want to perform append dataset, and there is 4 dataset to be appended, which data that will be appended last and how is the order?
3. In DOMO help page, i have seen that remove duplicates only work IF ONLY all columns in a row has the same value with the duplicates. Meanwhile I am doing experiment on two row with same id, remove duplicates will remove one of it even the rest of the columns value is different (But this is the result I am expected tho, since I want to remove row with same id, even other columns value is different)
++ : if DOMO could develop feature that make us possible to use append with "primary key like" feature in default data update, it should be more awesome! (Since current possible update choise is only replace and append)
Thanks DOMO, awesome product btw
Comments
-
Hello imam_ar,
I sent an email to you about this issue but have not heard back.
If this is still a concern please let me know by replying to that email. If this is no longer an issue please mark this issue as resolved.
Thank you,
-Tyler C.
Domo Employee
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Would you please also include the response in this thread as to help people (like me) who are wondering the same thing?
It would be very helpful to see the explanation in the same thread that we find where someone has the same or similar questions.
Thank you,
+Spencer
EDIT: I think I found a solution that fits my needs, here "https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-can-I-add-only-unique-values-from-one-dataset-to-another/td-p/16549"
The answer is the one by ilikenno with the screen shots. zcameron's response is the same concept, I think, but ilikenno's was more helpful to me with screen shots for the ETL.
Hope it helps.
1 -
Please email me the same thing you emailed this person. I need more help on this and would like to understand more about this.
0 -
Can we please have the response email in this thread. I had some issues and a detailed explanation would have been helpful
1 -
Ok, remove duplicates. Powerful and scary at the same time. I've killed myself over it in years past, not just in DOMO. I'm happy to share what I've learned. Hope it helps.
Here is our pretend data:
Shape Weight Color Date
Square 3 blue 2019-01-01
Square 3 blue 2019-02-01
Square 3 blue 2019-07-01
Square 23 blue 2019-05-01
Square 23 blue 2019-04-01
Square 3 red 2019-03-01
Circle 4 green 2019-01-01
Circle 4 green 2019-11-01
Circle 4 green 2019-03-01
Circle 10 orange 2019-07-01If I use [Remove Dulicates] and key only on the `Shape` field I will only get two rows. The simple, frustrating, hard truth is that I CANNOT control which two rows. The only thing I know for sure is that both "Square" and "Circle" will be represented. Here are some thoughts on this:
Both this...
Square 3 blue 2019-01-01
Circle 4 green 2019-01-01
And this are equally possible:
Circle 10 orange 2019-07-01
Square 23 blue 2019-05-01
This is NOT possible because
- red and 23 are never on the same row
- orange and 4 are never on the same row
Square 23 red 2019-04-01
Circle 4 orange 2019-07-01[Remove Duplicates] will not 'mix and match' values from various columns. It will pick one row and use that row.
The Real Question
I'm guessing the real question is, "How do I control which line is kept?"
This is possible. I would recommend that you use the [Rank & Window] action. If you are dealing with appended data or otherwise just want the record with the 'max date' or similar concept then also consider this article: Only using data from latest append
Rank & Window
Your first time trying to follow the setup screen for [Rank & Window] will likely be tricky, but it is not a difficult process. Honestly, filling out the boxes in Domo will be the hardest part and I think people quit there because they're not really sure it'll work anyways and when it doesn't run right they don't realize it was just a mix-match in the setup.
Before you go down this path you must ask if there is a way to identify which record you want by sorting on one or more columns. If the data is so ambiguous that you cannot create logic to single out the record you want then it won't be possible for Domo to get there either.
In our case we want the heaviest weight, and earliest date. We will use [Rank & Window] to sort the records that way and then add a `Row Number` based on that order. So 1 is the heaviest that is earliest date, and on down.
For Step 1 in the [Rank and Window Action] click "Add Function" which presents you with a new screen with it's own step 1 and 2:
- Name the field `Row Number` or something obvious like that.
- Pick 'Row Number' from the list.
- Click "Apply" in the upper right corner (you might have to scroll up). This step alone gets people lost an unable to continue.
Now, those were substeps of step 1, now we move on to the real step 2 and 3:
We will add two sets here, each with a column and sort order. The first column is `Weight` and the sort order is Descending. The second set is for column `Date` and is sorted Ascending.
Step 4 is our grouping. What field do we want to group by, in our case it is `Shape`.
Now we can preview and the data will look like this:
Shape Weight Color Date Row Number
Square 3 blue 2019-01-01 3
Square 3 blue 2019-02-01 4
Square 3 blue 2019-07-01 6
Square 23 blue 2019-05-01 2
Square 23 blue 2019-04-01 1
Square 3 red 2019-03-01 5
Circle 4 green 2019-01-01 2
Circle 4 green 2019-11-01 4
Circle 4 green 2019-03-01 3
Circle 10 orange 2019-07-01 1By playing with various setup choices for the field and sort order in steps 2 & 3 we can change that row number order until we like what we have.
Regardless of the order, I can now [Filter Rows] and filter to `Row Number` = 1 and my final data looks like:
Shape Weight Color Date Row Number
Square 23 blue 2019-04-01 1
Circle 10 orange 2019-07-01 1Hope this helps!!
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"12 -
This is awesome, thank you!
0 -
Ditto. This was very helpful, Mr. Clean! Thanks for taking the time to do this.
0 -
RANK & WINDOW + Filter will initially output the same number of rows that came in.
If Window Functions are not necessary, and you just need a SELECT DISTINCT equivalent, REMOVE DUPLICATES is OK except it keeps all your columns, so a good alternative (but equally slow to process) would be a GROUP BY with a Min / Max or a rowCount.
Technically I would expect GROUP BY to be marginally slower than REMOVE DUPLICATES because it's a true blocking function (i cannot know the results until i've processed all the rows) whereas REMOVE DUPLICATES could be a pass through function insofar as once I know a value is unique I can pass it onward through the ETL pipeline.
If performance is not as important as documenting the thought process, then choose the right tool for the job.
RANK WINDOW +FILTER if you're looking for first / last value.
GROUP BY for dimensional datasets or aggregations
REMOVE DUPLICATES if you're feeling lazy / need performance ;)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive