Hi Team,
I need assistance in Cross tab.
I have fields with multiple values in a column. I've transposed them, cleaned them. How do I put them together now?
Attached file has one tab for test data, and other with desired out put.
Please assist me.
Thanks,
Rana
Solved! Go to Solution.
Hi @itahir04
Based on your data, there is no way of knowing which data goes into which row. For example, how would Alteryx know whether Joe or Ben is software and who is hardware? Or does it not matter?
Kind regards
Kat
So as per @kat's comment, there is an issue in terms of identifying which function, etc, maps to the correct sales rep, I've made the assumption that this is done on the order of the data, i.e. the first function is for the first sales rep, and second is for the second sales rep and so on.
It also looks like you want to apply the customer name against every sales rep, even though it only appears once.
The multirow formula tool is a great method for creating an incrimentalID which is extremely useful in this case.
Here is a sample workflow I have built with the above conditions.
Ben
Hi @kat,
Please see my data know. I just want to separate multiple values that are in each column to separate row.
Data:
ID | Customer Name | Sales Rep | Product Category | Region | Product Sub-Category |
1 | Steph | Ben, Joe, Jen | Fruit, Drinks, Vegitable | North, East | Cider, spinich, Apple |
2 | Fran | Joe | Drinks | East, South | Coffe |
3 | Bob | Ben, Jen | Fruit, Drinks, Vegitavble, Baby Food | West | Butter, Cheese, IceCream |
Example out put for each record:
ID | Customer Name | Sales Rep | Prod Cat | Region | Prod Sub Cat |
1 | Steph | Ben | Fruit | North | Cider |
1 | Steph | Joe | Drinks | East | Spinich |
1 | Steph | Jen | Vegitable | Apple | |
I hope that explains it.
Thanks,
Rana
Hi @itahir04
@BenMoss's solution should help you achieve that.
I think it would be important for you to have a think though about what you want your data to show. Alteryx works on a row by row concept. This means that all calculations, etc are done at a row level. If your data is mixed between rows it might make calculations down the line difficult. If this is however just for presentation Ben's answer should help.
Agree with @kat comment on data structure. From your example it looks like you have comma separated lists you want to break to rows in each column.
You can do this with a couple of steps:
- Transpose to bring all fields into one column
- Split on commas using a Text to Columns tool
- Use a multi row formula to create a row id
- Cross tab back to get your wanted table.
my workflow is little lengthy though if you compare my workflow with @jdunkerley79
Hi @kat,
Actually yes that's what my data looks like. Some fields in the form allows multiple selection and an excel export shows Multiple values in one cell which needs to be separated.
Thanks.
Regards,
Rana