How is remove duplicate actually work?

imam_ar

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

Unknown

Hi all,

Can anybody help out @imam_ar?

Thanks!

Tclark07

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.

Cheers

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

 

@imam_ar

 

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.

user13734

Please email me the same thing you emailed this person. I need more help on this and would like to understand more about this.

rado98

Can we please have the response email in this thread. I had some issues and a detailed explanation would have been helpful

n8isjack

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:

1. Name the field `Row Number` or something obvious like that.
2. Pick 'Row Number' from the list.
3. 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   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!!

 

 

craigbosco

This is awesome, thank you!

Blind5quirrel

Ditto. This was very helpful, Mr. Clean! Thanks for taking the time to do this.

jaeW_at_Onyx

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 ;)