My sample data looks like the following: DateF1F2F3F4Amount1Amount21-1-2018tuaq50601-1-2018yibw-10-601-2-2018cpce-107091-2-2018bodr50020 The actual data is really huge and contains about 1 lakh records. I want to transform my data to get the below result: f1f2f3f41-1-2018_Amount11-1-2018_amount22-1-2018_amount12-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

Cross Tab to convert column to rows

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

Preparation

Accepted answers

SamDesk

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

Cross Tab to convert column to rows.yxmd

All comments

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

Cross Tab to convert column to rows.yxmd

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

Hey @alexis_d,

Sam

Cross Tab to convert column to rows.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703