MIP Logo

Generate Rows Tool –To Create New Rows of data – In Alteryx

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

 Generate Rows tool:

 

The Generate Rows tool can be used to create a new data set containing a field that iterates through values, starting with the Initialization Expression and with each rows the loop expression is applied to the previous value Until the condition expression is true. It has variety of uses.

In this blog, I will be explaining how generate rows tool is used :

A company in Australia has source data which is made up of a series of postal codes (e.g. 2000, 2001, 2002 etc.) among st some other data fields. They have a separate reference table which contains postcode ranges (e.g. 2000 to 2002) which they would like to use to match/filter their main data. In the below you can observe the Source and Reference input table.

 

 

 Source Table

 

 

 

 

 

 

 

Target table

 

 

 

 

 

My task is to get each Customer Record needs to be joined to the Lookup table based on a Postal Area Ranged region. Then finally summarize the customer data by Region, Sales Rep, and Responder, then a count of customers. In this way

 

Insight Support

 

 

 

 

 

 

 

 

 

 

 

Solution:

Step 1:

To get the above output first we need to join the Source and Reference input table to join this tables we need to see the common key in both the tables, we have Range from Source table and Postal Area from Reference table.

 Source Table                                                                                                   Reference Table

                                                                                

 

 

 

  • However, The above two columns cant be directly joined .So the source table has to be modified in such as it is same as reference table to be able to join.
  • So, split that range into another column by using Split to text Tool .

 

 

 

 

  •  After splitting the Range field into two columns as 1 and 2 in the above picture and it is named as Low and High Ranges by using Select statement.

 

Step 2:

The above splitting rows clearly helps us generate upper and lower limit ranges for an individual region R1. Once this is done, we can use Generate rows by using the below

Configuration:

  • First, Lets create a New field called as Row count
  • Now , Lets go through the 3 components in generate row tools:
Initialization Expression start the creation of rows. [Low Range]
Condition Expression where the condition is either true or false Row Count<= [High Range]

 

[High Range] field is an end point
Loop Expression that will generate subsequent rows until the condition (Condition Expression) is false [RowCount]+1

It will Increment the Low range value in the Rowcount field

 

 

  • By the above configuration, we can see that the Post code is split for R1 region from 2000 to 2019. Similarly, it is done for all regions.

 

 

STEP 3 :

  • Now we can Join the Source Table and Target Table on Postal Area and Rowcount

 

  • After join tool, we use summarize tool to count unique Customerid’s to get the desired output.
  • In Conclusion, We have learned how to use generate rows tool and its configuration features. 

Share this post