How to compare different lines against each other for complex filtering?

foppc

Hi,

I've joined two sets of data and the second set of data has Numbers that match the first set of data in some cases and in some cases there were corrections made. When I do the join, 2 line items become 4, because in many cases I'm duplicating data and need to filter it out, but in some cases like the below it wasn't exactly duplicated because there was a change for DataSet 1 to DataSet 2. DataSet 2 is the corrected DataSet, but I need to be able to compare and see what changes were made from DataSet 1 to DataSet 2.

In the below scenario. The final product I need is line 4 and line 1 to remain and lines 2 and 3 to be filtered out. Line 4 is easy to identify because there is a perfect match between the two Sets. Line 1 however requires me to compare data lines to one another ie (I need to compare line 3 to line 4 to one another to filter out line 3 and then I need to compare lines 1 and 2 to line 4 to then filter out line 2)

Its easy enough to find the matches and then add a match identifier column to compare against, but I've been unable to find a way to compare one line against another.

Encounter Id. Set 1 Date x Date y Set 2

52433771 2 99306 2022-09-30 2022-10-01 99310

52433771 2 99306 2022-09-30 2022-10-01 99497

52433771 4 99497 2022-09-30 2022-10-01 99310

52433771 4 99497 2022-09-30 2022-10-01 99497

I hope this makes sense, I found it difficult to describe. Please let me know if additional information or clarification is required.

Regards,

Chris

foppc

This screen shot is more clear than the text example in my post

RobSomers

@foppc In Magic ETL, you can use the Rank & Window tile with a LAG/LEAD function to create columns with values from different rows that you can use for your comparisons:

https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window