Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Duplicates rows but pairings match across different colums

8 - Asteroid



I'm looking at balances between companies comparing what is held in one company's books with another, and then vice versa (I had to do a self join to do this as it is all in one data source). This has resulted in duplicates (as expected due to the self join). I need to strip out these duplicates, but as you can see the criteria for identifying duplicates is in the opposite fields.


For example the Company 1 / Company 2 result is the same as the Company 2 / Company 1 result. I only need one of these - How can I strip the second pair out? I've tried several ways but can't seem to strip out the duplicates.


Company NameAF NameCompany USDAF USDDifference
Company 1Company 2   30,172,187.85- 30,104,689.36   60,276,877.21
Company 3Company 4     4,423,366.84-    4,347,685.16     8,771,051.99
Company 4Company 3-    4,347,685.16     4,423,366.84     8,771,051.99
Company 5Company 6           50,772.81-          89,725.92         140,498.73
Company 2Company 1- 30,104,689.36   30,172,187.85   60,276,877.21
Company 6Company 5-          89,725.92           50,772.81         140,498.73
16 - Nebula

Hi @PeterAP 


Here's one way:


  1. Add a record ID and transpose the fields with the company names
  2. Sort the names alphabetically
  3. Concatenate the names
  4. join the concatenations back to the data based on record ID
  5. Remove dupes, the first instance is kept





13 - Pulsar

Well, you can just use the Unique on the difference column. 

14 - Magnetar

@gabrielvilella although they're large numbers, it can't really be left to chance that they'll always be unique. On the off chance that there are multiple sets of companies with the same difference, Alteryx will drop all but the first combination. The solution provided by @Luke_C is fully dynamic and will ensure true uniqueness.

13 - Pulsar

You can just round the numbers first then use the unique.

8 - Asteroid

Thanks @Luke_C , this solution really worked for me!