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
Solved! Go to 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,
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!