Alteryx Tool - Make Columns

by Sijia Wei

We did predictive tools in training today with Gwilym and Bene. Instead of pivoting and joining the table we attached a Make Columns tool after the K Nearest Neighbours analysis to tidy up the data.

The tool takes rows of data and arranges them into multiple columns. Users can specify three things in the configuration pane:

  1. How many ‘Columns’ to create
  2. Whether they want the output to layout Horizontally or Vertically
  3. Whether to Group by any fields

I’ll use a very lovely data to explain what this tool does.

The following 8 examples will compare different combinations of the configuration options:

(1) 2 ‘Columns’, Horizontally

(2) 2 ‘Columns’, Vertically

(3) 3 ‘Columns’, Horizontally

(4) 3 ‘Columns’, Vertically

(5) 2 ‘Columns’, Horizontally, Group by: DS

(6) 2 ‘Columns’, Vertically, Group by: DS

(7) 2 ‘Columns’, Horizontally, Group by: DS and Gender

(8) 2 ‘Columns’, Vertically, Group by: DS and Gender

(It’s a bit confusing how ‘number of columns’ were counted in the Alteryx documentation,  I will try a different way to explain it.)

_______________________________________________________________________________

Comparing Horizontally with Vertically:

(1) 2 ‘Columns’, Horizontally

Configuration:

Output:

(2) 2 ‘Columns’, Vertically

Configuration:

Output:

There are 16 records (rows) in total in the original table, each row was treated as a whole since we didn’t group by any fields, so the output table has 2 ‘Columns’ * 8 rows. The Horizontally option fills the table from left to right, while the Vertically option fills the table from top to bottom.

_______________________________________________________________________________

What about 3 ‘Columns’?

(3) 3 ‘Columns’, Horizontally

Configuration:

Output:

(4) 3 ‘Columns’, Vertically

Configuration:

Output:

The output table has 3 ‘Columns’ * 6 rows (18/3=5.3, round up to 6).  Again, the Horizontally option fills the table from left to right, while the Vertically option fills the table from top to bottom.

_______________________________________________________________________________

Group by one field:

(5) 2 ‘Columns’, Horizontally, Group by: DS

Configuration:

Output:

(6) 2 ‘Columns’, Vertically, Group by: DS

Configuration:

Output:

This time Alteryx takes the field DS Group out from every record, so each ‘Column’ in the output only has the information about ID_Name and Gender. The Horizontally option fills the table from left to right firstly for DS8, then DS19, and the Vertically option fills the table from top to bottom for DS18, followed by DS19.

_______________________________________________________________________________

Group by two fields:

(7) 2 ‘Columns’, Horizontally, Group by: DS and Gender

Output:

(8) 2 ‘Columns’, Vertically, Group by: DS and Gender

Configuration:

Output:

Finally, when we Group by both DS Group and Gender, each ‘Column’ in the output only left with the field ID_Name. The Horizontally and Vertiacally option worked the same as before, this time in the order of DS18 Female, DS18 Male, DS19 Female, and DS19 Male.

_______________________________________________________________________________

Now we are clear about how the Make Column works, let’s go back to our Find Nearest Neighbours example:

We want to take row number 2, 4, 6, 8, 10 to the right hand side of row 1, 3, 5, 7, 9. That means we need 2 ‘Columns’ in the output table, and we want to fill the table from left to right. So this is how we configure the tool:

The output: