Challenge #1: Join to Range

[GeneR]

Here at Alteryx we know the importance of growing our experience at solving data and business problems. Almost weekly (~40+ times a year) we share an Alteryx challenge with our internal Alteryx users and then we all develop our own solutions.  Later we review our solutions in small groups. 

Since many Alteryx users have expressed an interest in having us share these exercises, we have created this new section of the Community. The intent is to share and exercise about once a week on Monday. Some of the exercises will be easy and some you will find more challenging.  The goal is to expose everyone to more of the things Alteryx can do and hopefully everyone will take away an idea or two on how to approach different challenges.  The following week we will post a new challenge as well as an example solution to the previous week's challenge. 

We hope you enjoy the exercises.

Exercise #1 Join to Range:

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.

Check and see what the result should look like by looking at the data labeled 'Output'.  Your mission is to take the input files and blend them so your result matches the output shown.  Good luck!

UPDATE 11/16/2015:

I have posted a solution to challenge #1 from last week.  It is a good example to show the usefulness of the generate rows tool.  Please keep in mind that this is just one solution using Alteryx to solve the problem, there can be many other solutions and approaches to the problem.  Hopefully you had fun and learned something new in the process.

[GeneR]

Here at Alteryx we know the importance of growing our experience at solving data and business problems. Almost weekly (~40+ times a year) we share an Alteryx challenge with our internal Alteryx users and then we all develop our own solutions.  Later we review our solutions in small groups. 

Since many Alteryx users have expressed an interest in having us share these exercises, we have created this new section of the Community. The intent is to share and exercise about once a week on Monday. Some of the exercises will be easy and some you will find more challenging.  The goal is to expose everyone to more of the things Alteryx can do and hopefully everyone will take away an idea or two on how to approach different challenges.  The following week we will post a new challenge as well as an example solution to the previous week's challenge. 

We hope you enjoy the exercises.

Exercise #1 Join to Range:

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.

Check and see what the result should look like by looking at the data labeled 'Output'.  Your mission is to take the input files and blend them so your result matches the output shown.  Good luck!

UPDATE 11/16/2015:

I have posted a solution to challenge #1 from last week.  It is a good example to show the usefulness of the generate rows tool.  Please keep in mind that this is just one solution using Alteryx to solve the problem, there can be many other solutions and approaches to the problem.  Hopefully you had fun and learned something new in the process.

[MarqueeCrew]

Now I'm really sorry that I don't have my laptop with me.  

[s_pichaipillai]

do we need to post our solution for the exercises :) 

[GeneR]

There is no need to post a solution, I will post one solution next Monday.  Keep in mind that my solution is only one way to solve the problem, and may not be the best way.  There are always many ways to solve a problem in Alteryx.

[cbridges]

OK, I'm a little late to this party, but I like it! I'm fairly new to Alteryx and self-taught, so I'm curious about your thoughts on my solution vs. yours.

SPOILER ALERT

Since Postal Code is an integer and there were only a few ranges, I just created a formula for Range in the customer database. If >=Start AND <= End then Start-End.

I know there are many, many different ways to do something, but at the same time I'm open to learning from others with more experience.

Is there any reason not to do it the way I did, or a reason you chose the split and multi-row solution as your go-to?

Thanks in advance for any insight.

[MarkN]

Just did this exercise. The solution that Gene offered is a more generalized solution because if we had a huge number of sales reps (let's assume over 1000), we won't be able to program all of that by hand in a formula in the customer database. 

[deepu]

Hi, I am new to this forum and I took the first challenge and I used the Tile compoent to obtain the same result. Please let me know if that is fine or the solution posted is the easiest way to obtain the result 

[GeneR]

No right or wrong way. Many ways to solve the same problem with alteryx...

Gene Rinas

[mceleavey]

Simple enough, and a nice introduction to the "Generate Rows" tool.

Spoiler

I took the range data and split the "Range" column into from and to columns. I then used the "Generate Rows" tool to generate all postcodes in the range between the from and to columns. I then simply linked back to the customer data feed on the "Postal Region" field, linking to the new range column I generated. 
Then it was a simple task of dropping the unwanted columns, grouping by the three key fields and creating a count of Customer IDs.

[Max06270]

Nice challenge, I used the formula into the generate row tool to recalculate the postal codes.

Spoiler