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
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.
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.
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-t..."
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.
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-01
If 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:
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 1
By 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 1
Hope this helps!!