Alteryx Weekly Challenge Solution 1

Challenge:

A company in Australia has source data which is made up of a series of postal codes (eg. 2000, 2001, 2002 etc.) amongst some other data fields. They have a separate reference table which contains postcode ranges (eg. 2000 to 2002) which they would like to use to match/filter their main data.
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.

Tools Used:

Solution Steps:

We need to create an area field from the range so that we can join it with the Postal Area given in the first database, then summarize it in our final step.

Steps:

1. Split Range column using Text to Column tool.

2. Change the data type of 2 new columns to Int32 so that we can use it as an integer.

3. Use the generate rows tool to create a new field ("Area").

    The configuration is as follows: Initialization Expression: [Start]

                                                        Condition Expression: [Area]<=[End]

                                                        Loop Expression: [Area] + 1

4. Join the above stream with the first data source input. Join Condition: Postal Area = Area

5. Summarize: Group By on Region, Sales Rep, & Responder. Count Customer ID.

This output is the result required.

Check the solution here.