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

Transpose & Grouping

Shelton_Thompson
8 - Asteroid

Hi Everyone, hope I can get help for this problem.

 

I have a workflow which looks for duplicates in records. I then use the grouping tool so I have single set's of duplicates to look at to ensure we do not mark the same record to both keep and deactivate. 

 

This results in this for example. As you can see there can be any number of matches from 1 to sometimes over 10.

GroupKey
60000000026500002038
60000000026500004673
60000000036500001977
60000000046500002028
60000000056500002034
60000000086500002031
60000000086500005622
60000000186000000701
60000000186000027351
60000000186500005486
60000000186500005715

 

In order to run the rest of my workflow to select a single survivor for each set of duplicates, I need to transpose the data to look like this where there is a single row for each group with the values filling each column. 

 

GroupKey 1Key 2Key 3Key 4
600000000265000020386500004673  
60000000036500001977   
60000000046500002028   
60000000056500002034   
600000000865000020316500005622  
60000000186000000701600002735165000054866500005715

 

I have tried the cross tab function, but the only way I can think of to get that to work is to assign a ID # (which is essentially a count) to each match so it would look like this where the count restarts for each group but I am unsure what tool to use to do this.

GroupKeyKey ID
600000000265000020381
600000000265000046732
600000000365000019771
600000000465000020281
600000000565000020341
600000000865000020311
600000000865000056222
600000001860000007011
600000001860000273512
600000001865000054863
600000001865000057154

 

I also tried the transpose tool to no avail. Any Ideas?

3 REPLIES 3
LordNeilLord
15 - Aurora

Hey @Shelton_Thompson

 

Multirow formula is the tool for this job!

 

MR.PNG

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

NicoleJohnson
ACE Emeritus
ACE Emeritus

You're almost there! Using the KeyID field, create another field that concatenates "Key " with your [KeyID] number for each record... and then use the Cross-Tab tool to get your data in the right layout. You will group by Group and then select your new field (the one that now reads "Key 1", "Key 2" etc.) as your header, and the Key field as your columns!

 

You could also use the Tile tool to get your KeyID numbers - always nice to see a new way to do it! Hope that helps :)

 

Cheers,

NJ

Shelton_Thompson
8 - Asteroid

Thank you so much! Worked perfectly!

Labels