community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #1: Join to Range

Alteryx Alumni (Retired)

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.

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

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

Alteryx Alumni (Retired)

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.

Bolide

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.

@thizviz
Atom

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. 

Atom

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 

Alteryx Alumni (Retired)
No right or wrong way. Many ways to solve the same problem with alteryx...

Gene Rinas
Alteryx Certified Partner

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

 

Spoiler
Solution.PNG

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.


Meteor

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

 

Spoiler
Capture.PNG