Column Values to Headers

[saachitalwar]

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!

[aatalai]

@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

[saachitalwar]

Can you please show this on an alteryx file

[DavidSkaife · Accepted solution]

Hi @saachitalwar 

Here is one attempt at it: