Table transformation

[Pisti93]

Hi All,

Do you have a working solution for the below problem?

My data looks like this now:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6
Value 1	Name 1	5	9	8	0
Value 2	Name 1	3	9	3	2
Value 3	Name 1	12	13	10	0
Value 4	Name 1	18	3	2	0
Value 1	Name 2	20	13	11	10
Value 2	Name 2	16	16	11	2
Value 3	Name 2	11	5	5	10
Value 4	Name 2	15	6	5	17

The layout I would like to get:

	Name 1	Name 1	Name 1	Name 1	Delta	Delta	Delta	Delta	Name 2	Name 2	Name 2	Name 2
Column 1	Column 3	Column 4	Column 5	Column 6	Delta Column 3	Delta Column 4	Delta Column 5	Delta Column 6	Column 3	Column 4	Column 5	Column 6
Value 1	5	9	8	0	-15	-4	-3	-10	20	13	11	10
Value 2	3	9	3	2	-13	-7	-8	0	16	16	11	2
Value 3	12	13	10	0	1	8	5	-10	11	5	5	10
Value 4	18	3	2	0	3	-3	-3	-17	15	6	5	17

Thanks for the help.

[PhilipMannering]

Unfortunately you can't have nested headers in Alteryx.

This is as close as I could get...

[Ladarthure · Accepted solution]

Hi @Pisti93,

Your problem is about transposing and making cross tab and transposing to get the data formated how you want. The trick in this case, is mostly to focus to have a dataset formated in columns, then create the calculations needed and finally transpose and cross tab again to have it presented as you want.

Here are detailled steps :

1. Transpose data by having key columns as column 1 and 2 to keep them as they are and have the rest of the data transposed
2. use a cross tab with column 2 as a new header to be able to calculate the delta (you will need to keep column 1 and Name as group by values)
3. calculate the delta
4. use a transpose to have one column with name1/name2/delta (one row per value)
5. create a new column name by combining NAME and NAME2 which will give column n and the name n or delta.
6. This done, you can do a final cross tab to have the data presented the way you want.

If explanations are unclear, tell me

[Pisti93]

This solution worked as charm. Thanks for the quick response. Do you have a solution in case the number of "Name" is more than 2? I mean now only "Name 1" and "Name 2" exist but what if also "Name 3" is there? (Also we can't be sure about the exact number)

Delta1 should be the same as now (like in your solution):

Delta2 should be [3] - [2] in case [3] exists, Delta3 should be [4] - [3] in case also [4] exists and so on...

In this example the number of delta columns is dynamic depending on the number of other columns. Is it possible to solve it without writing a macro in python?

Thanks!