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

Cet Temp.xlsx

Preparation

Accepted answers

BenMoss

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

ExampleWorkflowCrossTab.yxmd

jdunkerley79

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.

Cell level CSVs.yxmd

ponraj

@itahir04, here you go!

Workflow Results Results

Cross Tab.yxmd

All comments

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

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

ExampleWorkflowCrossTab.yxmd

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