Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Distinct values with two columns

CyrilB
5 - Atom

Hi there,

 

I have something simple to do but I only find complexs way to do it so I must miss something.

 

Let Say I have two columns TYPE AND TYPE2 which will contain the same domain values.

 

I want to have the distinct value whatever the order of the values.

 

TYPE        TYPE2

AAA           BBB

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

 

At the end I want only the distinct combinaisons :

 

TYPE        TYPE2

AAA           BBB

AAA           CCC

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

 

Any ideas ? 

 

Thanks

4 REPLIES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

You can do this using the Unique Tool combined with a bit of unioning!

 

Use a select tool to create a set with Type and Type2 reversed

 

Then union and finally Unique tool

 

I take it back. Use a formula tool to prepare a new column:

IIF([Type]<[Type2],[Type] + [Type2],[Type2] + [Type])

Do the unique on that then think will be fine.

 

Attached workflow showing what I mean

CyrilB
5 - Atom

No that's the first thing I tried but it doesn't recognize the cross references. It's just a select distinct 

 

Unique tool results are 

 

TYPE        TYPE2

 

 

AAA           BBB

AAA           CCC

AAA           CCC

BBB           AAA

BBB           CCC

CCC          BBB

jdunkerley79
ACE Emeritus
ACE Emeritus

yep adjusted my reply - agree immediate first guess wouldnt work

CyrilB
5 - Atom

I should be able to manage it with your second proposal even without the unique tool, I can simply take the first occurence of your formula result.

 

Thanks I'll confirm shortly

 

Kind regards

Labels