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
Ranking | Fruits | Vegetables | ||||
Type | Amount | Cost | Type | Amount | Cost | |
1st | Apple | 5 | 200 | Celery | 7 | 70 |
2nd | Pineapple | 6 | 100 | Spinach | 4 | 120 |
3rd | Banana | 3 | 500 | Cabbage | 2 | 50 |
4th | Mangoes | 2 | 50 | Broccoli | 10 | 160 |
5th | Strawberry | 8 | 140 | Eggplant | 3 | 100 |
and I would like to transform it to this format:
Ranking | "Group" | Type | Amount | Cost |
1st | Fruits | Apple | 5 | 200 |
2nd | Fruits | Pineapple | 6 | 100 |
3rd | Fruits | Banana | 3 | 500 |
4th | Fruits | Mangoes | 2 | 50 |
5th | Fruits | Strawberry | 8 | 140 |
1st | Vegetables | Celery | 7 | 70 |
2nd | Vegetables | Spinach | 4 | 120 |
3rd | Vegetables | Cabbage | 2 | 50 |
4th | Vegetables | Broccoli | 10 | 160 |
5th | Vegetables | Eggplant | 3 | 100 |
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.
Solved! Go to Solution.
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.
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!