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
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.