ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Text Pivot .... should be simple ... but.. suspect I'm missing something obvious

Watermark
8 - Asteroid

Ok, I've searched conversations and not found my answer, and spent way too much effort solving what should be an easy point and I 'know' I'm just missing it.  The below isn't the real data, but this is short easy and follows same concept, my real data set is much too large. 

 

I want to flip this with and use  Car & Home as column headers: 1 row per each data set per day

 

Day        Place     Mood

Mon       Home    happy

Mon       Car         sad

Tue        Home    sleepy

Tue        Car         sleepy

Wed       Home    happy

Wed       Car         happy

Thu        Home    quiet

Thu        Car         happy

Fri           Home    bored

Fri           Car         mad

Mon       Home    mad

Mon       Car         bored

Tue        Home    happy

Tue        Car         quiet

Wed       Home    happy

Wed       Car         happy

Thu        Home    sleepy

Thu        Car         sleepy

Fri           Home    happy

Fri           Car         sad

 

 

 

My real data set has more than 2 columns, and real answers are more than 4. I know there has to be an easy answer I'm missing.  Transpose / Crosstab doesn't work with 'first' or 'concatenate'. 

 

Thoughts?

 

apathetichell
13 - Pulsar

Does this work?

fmvizcaino
16 - Nebula
16 - Nebula

Hi @Watermark ,

 

Here it is.

Since you don't have an unique ID for each day, I've used a multi-row tool to generate it. But if you have a date column, you can use the tile tool to generate this ID as well.

 

This ID is needed to work as a key field in cross-tab tool.

fmvizcaino_0-1620159925589.png

 

Best,

Fernando Vizcaino

 

Watermark
8 - Asteroid

So effectively you add an ID for each 'group' of records .  In my example it was a day, but for my real set, I just need to determine a pattern for the count. 

I think that works. 

An ace just answered with effectively same solution. Multi-row create ID for each record grouping.  Thanks!

Labels