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

Union + Crosstab without alphabetical order?

km007
7 - Meteor

Hello!

 

I have a several inputs that all have roughly the same format except with a different amount of rows. The format is a list of items followed by an amount of items, but some files have more items than other. For example, File 1 might have

 

Apples 5

Orange 3

Bananas 6

 

and File 2 might just have

 

Apples 3

Bananas 10

 

I want to do a union and crosstab to get a result similar to this:

 

Fruit              FIle 1        File 2

Apples           5                 3

Orange          3 

Bananas        6                 10

 

I don't have a problem getting to this point but I'm having some trouble preserving the item order. I tried to do record ID and group by RecordID in the cross tab tool but that doesn't work because each file has a different amount of rows therefore Apples might not have the same RecordID in each file causing the output to have a row for each instance of Apple rather than showing up in one row. Any help would be greatly appreciated, thanks!

12 REPLIES 12
km007
7 - Meteor

I tried using the UNIQUE tool but the output re-sorts it into alphabetical order. I'm guessing this is because Alteryx needs to sort it in order to find duplicates to delete? Unfortunately, that doesn't preserve the order for me unless I'm using the tool incorrectly.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@km007,

 

If you send me a private message with your email address, I'll setup a webex for us at 10:00 am Eastern tomorrow (Friday).

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
km007
7 - Meteor

Thanks so much for the offer. I couldn't figure out how to send a private message so had to respond here haha. Do you mind sending me one? I ended up creating a mapping file externally and using it as an input. It's not the best solution because it's not dynamic but I had to finish my draft workflow today. I can't make the Webex tomorrow due to work but I'd love to hear any thoughts you have another time. 

Labels