Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Table transformation

Pisti93
6 - Meteoroid

Hi All,

Do you have a working solution for the below problem?

 

My data looks like this now:

Column 1Column 2Column 3Column 4Column 5Column 6
Value 1Name 15980
Value 2Name 13932
Value 3Name 11213100
Value 4Name 118320
Value 1Name 220131110
Value 2Name 21616112
Value 3Name 2115510
Value 4Name 2156517

 

The layout I would like to get:

 Name 1Name 1Name 1Name 1DeltaDeltaDeltaDeltaName 2Name 2Name 2Name 2
Column 1Column 3Column 4Column 5Column 6Delta Column 3Delta Column 4Delta Column 5Delta Column 6Column 3Column 4Column 5Column 6
Value 15980-15-4-3-1020131110
Value 23932-13-7-801616112
Value 31213100185-10115510
Value 4183203-3-3-17156517

 

Thanks for the help.

3 REPLIES 3
PhilipMannering
16 - Nebula
16 - Nebula

Unfortunately you can't have nested headers in Alteryx.

 

This is as close as I could get...

PhilipMannering_0-1653481472310.png

 

Ladarthure
14 - Magnetar
14 - Magnetar

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
6 - Meteoroid

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):

Pisti93_0-1653512664609.png

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!

 

Labels