Alteryx in the office – episode 2

I had a task the other week where I needed to load Excel data into a datamart. The problem was that the Excel data was not formatted like my destination table in the datamart. So I fired up Alteryx and started playing around.

The Excel sheet was structured with total quantity by week, like so:

ScreenHunter_01 Feb. 25 20.11

(sample data shown)

But I needed it to appear as follows:

ScreenHunter_02 Feb. 27 11.15

The goal is to have total distinct job_name rows equal the QTY value for the corresponding Week of. So 1/3/2011 would have a total of 117 rows.

My result was the following module.

ScreenHunter_03 Feb. 27 11.18

First, I bring in the data with an Input tool. Then I used a Formula tool to create a string field as “jobname”. The next part is where it gets fun. There’s a Generate Rows tool in the Preparation section. I set it up to create a new field, RowCount, initialized at value 1. You can then set the Condition Expression, which for me was RowCount<=[QTY]. The loop iteration is simply RowCount +1 in order to increment rows by 1 each time.

ScreenHunter_04 Feb. 27 11.21

This created rows that match the QTY value for each Week of. Next came the a Record ID tool followed by another Formula tool so that I could create distinct job_names. This is simply a concatenation of the original jobname field with Record ID. I also added a Quantity formula of 1 for quick summation purposes.

ScreenHunter_05 Feb. 27 11.36

A final Select tool was used so I only outputted my desired columns. You can download the sample input, Alteryx module, and sample output files here.

Hope you enjoyed it!

Share Button

Leave a Reply