Alteryx Designer

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

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

Highlighted
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

 

 

Highlighted
14 - Magnetar
14 - Magnetar

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

Highlighted
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

Highlighted
5 - Atom

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

 

Labels