Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Converting rows to columns - data preparation for advance analytics and machine learning

Sall
5 - Atom

I have a table with values extracted from unstructured data that looks somewhat like this:

 

Country 1

country 2

Country 3

Country 4

Country 5

Country 6

Germany 

Turkey      

null

null

null

Null

Denmark 

Spain         

Portugal 

Turkey

null

Null

Romania  

Germany

null

null

null

Null

Romania  

null

null

null

null

Null

 

In order to use the data for machine learning and advance analytics I need to convert it to this structure:

               

Germany

Denmark

Romania

Turkey

Spain

Portugal

1

0

0

1

0

0

0

1

0

1

1

1

1

0

1

0

0

0

0

0

1

0

0

0

 

I have being working with SQL statements, cursors, ifs' and more in order to solve this problem. And it is a little pain in the a**. It can literally save me a ton if I could Alteryx it somehow.

 

Thanks in advance to some of the great minds around here

 

 

3 REPLIES 3
Kenda
16 - Nebula
16 - Nebula

Hey @Sall!

 

This problem shouldn't be too hard in Alteryx. I have attached a workflow that details how I would solve it. I start by adding a RecordID tool. Then the key is to transpose the data. After that, it is just some clean up and then using a CrossTab to get the data back with the countries as the headers.

 

Hope this helps!

 

Capture.PNG

DavidxL
9 - Comet

Was just about to post but @Kenda beat me to the punch! Only thing I did differently was using the Data Cleansing tool at the end instead of Multi-Field Formula, but both come up withe same answer.

 

Capture.PNG

Sall
5 - Atom

Amazing.Thank you!
I'm quite new to the Alteryx world. and I am loving it. 

 

Labels