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

Crosstab?

G_SAND
8 - Asteroid

Hi All

 

I think I need to use a Crosstab for this but I can't seem to work it out

Current Layout

MaterialUse
A1
A2
B1
B2
B3

 

Required Layout

MaterialUseUseUse
A12 
B123

 

I'm sure it is easy. Any help would be great

 

Thanks

7 REPLIES 7
NicoleJohnson
ACE Emeritus
ACE Emeritus

See attached, I think this will get you what you are looking for (though slightly different format than how you've displayed above). I'm assuming that what you want is the different uses across the top, and than an indicator of whether or not that material is needed for that use case. So in my example results, it's showing a count of 1 for every use, rather than the 1 2 3 that you are showing (since 1 2 3 would be your column headers instead).

 

1. Summarize by Material & Use, then count Use

2. Crosstab with Group by Material, Use as column headers, and Count as values.

 

Let me know if this gets you what you need!

 

Cheers,

NJ

G_SAND
8 - Asteroid

Hi Nicole

Thanks for the reply but in fact I do need the numbers stated in the table (1,2,3 etc) across from the material.

NicoleJohnson
ACE Emeritus
ACE Emeritus

No problem! The key with the Cross-Tab tool is that you have to have a different field for header & values... but nothing says they can't be the same values. So if you add a formula tool before a Cross-Tab, you should be able to get the layout you need. Note that you cannot have multiple columns with the same name, so in the solution attached I'm showing it as 1 2 3 instead of Use Use Use. But you could alter the formula to be something like "Use 1", "Use 2", "Use 3", etc.

 

Does that get you closer?

 

NJ

G_SAND
8 - Asteroid

 Hi Nicole

 

We are getting closer but I may have been too simplistic in my example. Here's a better indication on the data:

MaterialUse
A0.1
A0.6
B1
B0.8
B1.2

 

Hopeful output:

 

MaterialUse1Use2Use3
A0.10.6 

B

10.81.2

 

Following your last example, I suppose I need to put some kind of indicator in a new column so I can use the cross tab function:

 

MaterialUseIndicator
A0.11
A0.62
B11
B0.82
B1.23

 

Would that work? And if so, how can I add that extra column? In the data set itself there are materials with up to 10 entries. 

 

Thanks

NicoleJohnson
ACE Emeritus
ACE Emeritus

Yes, that's exactly how you could achieve that. Easiest is probably the Tile tool. You can mark your unique field as Material - this will give you a Tile_Num and Tile_SequenceNum field - the Tile_SequenceNum field is the one you'll want to use that shows you the identifier per Material. Use that field to create your "Use #" headers, followed by the Cross-Tab.

 

See attached! :)

 

NJ

G_SAND
8 - Asteroid

Hi Nicole

 

We got there in the end!!

 

Thanks for your help.

becki
8 - Asteroid

thank you for posting the solution!  i could use it on my own question!

Labels