Alteryx Designer Discussions

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

Duplicates rows but pairings match across different colums

PeterAP
8 - Asteroid

Hi,

 

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
5 REPLIES 5
Luke_C
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

 

Luke_C_0-1655141672862.png

 

 

gabrielvilella
13 - Pulsar

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

DataNath
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.

gabrielvilella
13 - Pulsar

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

PeterAP
8 - Asteroid

Thanks @Luke_C , this solution really worked for me!

Labels