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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
jmuskovitz
6 - Meteoroid

Have you ever had the problem where your data has lots of irregular holes, but that missing data is actually important to you? The Generate Rows tool can help!

Let's take a simple example. Suppose you have a dozen bins of widgets, and you know how many widgets are in them, but you only have counts for bins that aren't empty.

 

Bin Count
1 20
5 25
8 15
12 6

 

If you were to take this data and chart it in Alteryx, you'd get something like this:

 

but what you really want is something like this:

 

The easy way to do this is to use the Generate Rows tool to create empty records (one for each bin) and then join the sparse data to these rows. To do this, you need to know the lower and upper limit of the range you want to build out, but often this depends on the data itself. If that is the case, you can use the Sample tool to find the first and last records, then use these in your Generate Rows tool. In this example, I simply told the tool to go from 1 to 12. Here is what the module looked like:

 

 

In a future post, I'll explain how I used this technique to turn a simple list of holidays (or other events) into an HTML wall calendar, but for now, have fun smoothing out your sparse data!