The Alteryx community challenges offer opportunities to improve your Alteryx skills. I have decided to participate in these challenges on a regular basis. So, to begin, I have completed Challenge #1: Join to Range, and would like to share a brief walkthrough of my completion process. You can find this challenge in the following link.
Challenge #1: Join to Range
Source File
Lookup Table
My Solution
Approach to Solution
Step 1: Split Range from the Lookup Table into two Columns Range 1 and Range 2
We can use 'Text to Column tool" to split the data in the 'Range' column into Range1 and Range2 by the help of the delimiter '-'.
Step 1: Result
Step 2: Change the data type of Range1 and Range2 into Int
We can use the 'Select' tool to change the data type. In the next step, we need to use these fields in calculations to generate rows.
Step 3: Create a column, 'PostalCode' where new rows are generated for each record based on Range1 to Range 2.
For example, we want to transform:
We use 'Generate Rows' tool for this purpose
Step 3: Result
Step 4: Remove unnecessary columns and change data type of 'PostalCode'
We use 'Select' tool to remove unnecessary columns. Since we need same data type for 'Postal Area' in source data and 'PostalCode' in lookup table to perform JOINs we need to change the data type of 'PostalCode'
Step 4: Result
Step 5: Perform JOIN to blend data source and lookup table
We can use 'JOIN' tool to blend data source and lookup table by a common field 'Postal Area' (data source) and 'Postal Code' (lookup table).
Step 5: Result of Inner Join (J)
Step 6: Summarize the customer data by Region, Sales Rep, and Responder, then a count of customers
We use 'Summarize' tool to group the data by Region, Sales Rep and Responder using 'Group By' function and then count the customers by 'Count' function.
Final Result:
