Dynamic crosstab?

[TSP]

Hi everyone.

I'm struggling to convert the following:

Input:

Field1	Field2	Field3	Total_Sales	Total_Profit
ABC	A	Z	10	60
ABC	B	Y	20	70
ABC	C	X	30	80
DEF	D	W	40	90
DEF	A	Z	50	100

I'd like to take field 1 and add it as a prefix to my two numerical columns (total sales & total profit):

Field2	Field3	ABC_Total_Sales	ABC_Total_Profit	DEF_Total_Sales	DEF_Total_Profit
A	Z	10	60	50	100
B	Y	20	70
C	X	30	80
D	W	40	90

Many thanks,
TSP

[Thableaus · Accepted solution]

Hi @TSP 

Here's a solution

- Transpose your dataset on data fields Total_Sales and Total_Profit

- Concatenate your column Field 1 with the Name column created

- Use the CrossTab using now as column headers this new column recently created. Value as the Value column generated by transpose tool.

Workflow attached.

Cheers,

[LukeG · Accepted solution]

@TSP 

Here is a solution that should accomplish what you are looking for.

These three steps should complete the challenge. Here is a preview of the output:

Workflow attached.

Please let me know if you have any questions! Happy to help!

[TSP]

Thanks both @LukeG  and @Thableaus 

Many thanks,

Tim