Cross Tab

[itahir04]

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

[kat]

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

[BenMoss · Accepted solution]

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

[itahir04]

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

[kat]

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.

[jdunkerley79 · Accepted solution]

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.

[ponraj · Accepted solution]

@itahir04, here you go!

WorkflowResults

[ponraj]

Here is another sample workflow for your case. 

WorkflowResults

[ponraj]

my workflow is little lengthy though if you compare my workflow with @jdunkerley79

[itahir04]

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