Cross Tab to convert column to rows

[alexis_d]

My sample data looks like the following:

Date	F1	F2	F3	F4	Amount1	Amount2
1-1-2018	t	u	a	q	50	60
1-1-2018	y	i	b	w	-10	-60
1-2-2018	c	p	c	e	-10	709
1-2-2018	b	o	d	r	500	20

The actual data is really huge and contains about 1 lakh records. I want to transform my data to get the below result:

f1	f2	f3	f4	1-1-2018_Amount1	1-1-2018_amount2	2-1-2018_amount1	2-1-2018_amount2

Right now, I am using two cross tabs in parallel where in Cross tab 1 - I take New Colum headers as Date and value as sum of amount1 and I add another cross tab 2 - I take column headers as Date and value as sum of amount2. But, it's getting very difficult to align them and name them

What's the most efficient way of doing it?

Thanks in advance

[SamDesk]

Hello @alexis_d,

To aid in your custom renaming of cross-tab headers, you can make use of the Dynamic Rename tool.

Here we split your data stream into X streams, where X is the different number of Amount fields you have. In the example you provided, that's just Amount1 and Amount2 so we have split into 2 streams.

After performing the cross-tab for each individual Amount field we then use the Dynamic Rename tool to add in the delimiter "_AmountY", where Y is the current Amount field number. For the top stream, and Amount1, the formula looks like this:

[_CurrentField_]+"_Amount1"

The tool is configured like the following. I have left Dynamic or Unknown Fields checked so when you add in your dataset with the full list of dates, these will automatically be included in the rename process.

Hope this helps!

Sam :)

[alexis_d]

Hey,

Thanks for the solution. Also, is this the most efficient way of doing the cross tab? or is there any way I can do this data transformation?

[SamDesk · Accepted solution]

Hey @alexis_d,

You could make it more efficient if you first transpose the amount fields. You could then create a concanated Header field that brings together Date and the transposed amount field name.

Sam :)

[alexis_d]

Sam - Thanks a lot. Saved a lot of trouble .

[SamDesk]

No problem at all @alexis_d, happy to have helped!

Sam :)