I have a table in the form:
date | rate | |||
type1 | type2 | |||
date1 | A | val1 | A | val9 |
[Null] | B | val2 | B | val10 |
date2 | A | val3 | A | val11 |
[Null] | B | val4 | B | val12 |
date3 | A | val5 | A | val13 |
[Null] | B | val6 | B | val14 |
date4 | A | val7 | A | val15 |
[Null] | B | val8 | B | val16 |
and I wish to transform it into:
type1 | type2 | |||
dates | A | B | A | B |
date1 | val1 | val2 | val9 | val10 |
date2 | val3 | val4 | val11 | val12 |
date3 | val5 | val6 | val13 | val14 |
date4 | val7 | val8 | val15 | val16 |
I tried cross tab but the 4 values need to be aggregated, I just want the values as they are in a column.
Thanks!
Solved! Go to Solution.
@saachitalwar you need to start by changing the Strucure.
I would start by using the multi row tool to give each row a date with if row:date != null() then row:date else row-1:date endif
then restruter the data selecting the date and the type 1 columns and then use another selct tool, selecting date and the type 2 columns
REname the the type names to A1,B1 and A2,B2 using the forumla tool.
Then stack them together using the union tool
Then cross tab it setting get column names from what the A1,B1,A2,B2 field is called and get values from the values field and group by date.
Let me know how you get on
Can you please show this on an alteryx file