Transforming data using crosstab

[c_raviram]

Hello Experts,

I have a data in below format.

Controls	Treatments	Distance
7162	1664	0.478594543
8112	1664	1.034442878
1580	1675	0.456339769
1807	1675	0.560453609
1964	1696	0.312367277
1863	1696	0.489136816
2014	1700	0.810401862
1630	1700	0.916179888
8162	1712	0.671440615
7434	1712	0.793269339
9081	2288	0.277931764
2568	2288	0.714133664
12219	2293	0.348582519
9524	2293	0.656037648
3102	2301	0.381248049
9238	2301	0.43464612
2409	2322	0.171431367
3235	2322	0.451250302
12536	2341	0.397959588
2383	2341	0.423791644

I have to convert the data in below format.

Treatment Store	Control Store 1	Control Store 2
1664	7162	8112
1675	1580	1807
1696	1863	1964
1700	1630	2014
1712	7434	8162
2288	2568	9081
2293	12219	9524
2301	3102	9238
2322	2409	3235
2341	12536	2383

Could any of you please assist in transforming the data as mentioned above,

File is attached for ready reference.

Regards, Ravi

[AngelosPachis · Accepted solution]

Hi @c_raviram ,

First thing you have to do is to create a field that will differentiate data between store 1 and 2. As data for stores 1&2 come in sequence, you can do that by first assigning a record ID in each row and then using the modulo function to group them in stores 1 & 2.

Then you can use a cross-tab tool to bring your data in the desired format

Let me know if you have any questions. Hope that helps.

Regards,

Angelos

[marcusblackhill · Accepted solution]

Hey @c_raviram !

in your example you have the max of 2 control stores, but assuming you can have more than 2, I did the example below, parsing in many columns as you need.

Hope that help!

[Qiu · Accepted solution]

@c_raviram 
I usually use Tile Tool for this situation.