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

Moving Certain Column Values to Bottom Row

see-seo
7 - Meteor

Hi Community,

 

I desperately need help on knowing how to convert from 'Image 1' to 'Image 2'

 

I basically want the Pizza, Taco, Coke, and Fries sorted by Code. But the Code will repeat itself until all the values for the foodies are filled in.

 

Thank you for your effort in advance.

 

I am attaching an example model.

 

1122

15 REPLIES 15
MarqueeCrew
20 - Arcturus
20 - Arcturus
Are the repeating groups dynamic? How many sets are expected or do you know?
Alteryx ACE & Top Community Contributor

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

Hi MarqueeCrew,

 

I want the groups to be dynamic, but that would be the next step since the data quality is not great right now.

 

And there are about.. 20-30 sets.

MarqueeCrew
20 - Arcturus
20 - Arcturus
See if you can follow this path:

Put a record ID on
Group by ID and transpose data

Use a formula:

Regex_Replace([name],".*(\d+)",'$1') to get a new string field for sets.

Regex_Replace([name],"(.*)(\d+)",'1') to get a new string field for stuff.

Now crosstab by ID and sets as groupby and have stuff as header and value as value.

This or something close to it will get you there.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

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

Hi Mark,

 

I am a beginner so I am having trouble fully understanding your solution.

 

Could you make a alteryx model and share by replying?

 

Thank you.

danrh
13 - Pulsar

Something like this should do the trick.  Similar to @MarqueeCrew's solution:

 

image.png

john_miller9
11 - Bolide

This post was interesting as it took me a few tries to solve.  At first, I started down the path of simply transposing the columns and parsing the text from the number like @MarqueeCrew had mentioned, and was going to crosstab from there as @danrh showed in his solution.  However, in the desired output it was the number of food types that dictates the number of rows per code, not the numeric digit from the original column name.  The workflow below should accomplish this.  Thanks,

 

jm

 

Foodies_Solution.PNG

 

see-seo
7 - Meteor

Hi John,

 

I appreciate you going through the problem with us but I wasn't able to open your file because it was created with a newer version.

 

I have v11.5.1.31573.

 

Could you please downgrade your file and repost?

 

Much appreciated in advance!

 

 

john_miller9
11 - Bolide

@see-seo See if this works for you.  I just opened the file in notepad++ and replaced 11.7 with 11.5 in the xml.  That should work, but let me know if it doesn't.  Thanks,

 

jm

 

see-seo
7 - Meteor

Hi John,

 

Thank you so much for going through the trouble, but could you try replacing to 11.3 instead?

 

I still get the same error message, but changing to 11.3 would work based on this link:

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

 

Thank You!

Labels