ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Transforming data using crosstab

c_raviram
6 - Meteoroid

Hello Experts,

 

I have a data in below format.

 

ControlsTreatmentsDistance
716216640.478594543
811216641.034442878
158016750.456339769
180716750.560453609
196416960.312367277
186316960.489136816
201417000.810401862
163017000.916179888
816217120.671440615
743417120.793269339
908122880.277931764
256822880.714133664
1221922930.348582519
952422930.656037648
310223010.381248049
923823010.43464612
240923220.171431367
323523220.451250302
1253623410.397959588
238323410.423791644

 

I have to convert the data in below format.

 

Treatment StoreControl Store 1Control Store 2
166471628112
167515801807
169618631964
170016302014
171274348162
228825689081
2293122199524
230131029238
232224093235
2341125362383

 

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

 

File is attached for ready reference.

 

Regards, Ravi

AngelosPachis
15 - Aurora

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

Screenshot 2020-11-28 204358.jpg

 

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

 

Regards,


Angelos

 

marcusblackhill
11 - Bolide

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.

 

marcusmontenegro_0-1606596910596.png

 

Hope that help!

 

 

Qiu
17 - Castor

@c_raviram 
I usually use Tile Tool for this situation.

1129-c_raviram.PNG

Labels