Free Trial

Alteryx Designer Desktop Discussions

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

Need Help in Data manipulation

salvador_ian
7 - Meteor

Hello Community,

 

Does anyone know a smart way in transforming this set of given data? 

I made a sample based on the data I am working with, please see below:

This is the original format

RankingFruits  Vegetables  
 TypeAmountCostTypeAmountCost
1stApple5200Celery770
2ndPineapple6100Spinach4120
3rdBanana3500Cabbage250
4thMangoes250Broccoli10160
5thStrawberry8140Eggplant3100

 

and I would like to transform it to this format:

Ranking"Group"TypeAmountCost
1stFruitsApple5200
2ndFruitsPineapple6100
3rdFruitsBanana3500
4thFruitsMangoes250
5thFruitsStrawberry8140
1stVegetablesCelery770
2ndVegetablesSpinach4120
3rdVegetablesCabbage250
4thVegetablesBroccoli10160
5thVegetablesEggplant3100

 

Please consider that there could be more of "Groups" like vegetables and fruits, for example like snacks, desserts and side dishes so I need to make the Alteryx workflow to be able to transform the original data if more "Groups" is added in the future.

Thank you in advance for any of your help!

Best regards.

2 REPLIES 2
gawa
16 - Nebula
16 - Nebula

@salvador_ian 

As your input data has multi-line headers, it makes data process complicated. 

First, using the first 2 rows, you should create new field name that are unique and identifiable.

Second, use the Transpose and Cross-tab tools, reshape data into the desired format. These tools enable your WF to accommodate dynamic input schema having unknow number of columns(groups in your context) by properly configuring settings.

 

Here is WF to realize the above process.

 

image.png 

salvador_ian
7 - Meteor

@gawa 

Thank you very much!

This works like magic. I learned a lot from this WF. I was also having trouble in multi-line headers, so this is a big help!

 

Labels
Top Solution Authors