topic Re: Challenge #1: Join to Range in Weekly Challenges

Challenge #1: Join to Range

GeneR — Fri, 05 Jul 2024 21:12:47 GMT

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.

Re: Alteryx Weekly Challenge - Week #1 Join to Range

MarqueeCrew — Mon, 09 Nov 2015 22:46:37 GMT

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

Re: Alteryx Weekly Challenge - Week #1 Join to Range

s_pichaipillai — Tue, 10 Nov 2015 11:34:06 GMT

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

Re: Alteryx Weekly Challenge - Week #1 Join to Range

GeneR — Tue, 10 Nov 2015 13:39:52 GMT

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.

Re: Alteryx Weekly Exercise - Week #1 Join to Range

cbridges — Mon, 07 Dec 2015 23:03:45 GMT

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.

Re: Alteryx Weekly Exercise - Week #1 Join to Range

MarkN — Thu, 16 Jun 2016 05:23:02 GMT

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.

Re: Challenge #1: Join to Range

deepu — Tue, 20 Dec 2016 17:01:18 GMT

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

Re: Challenge #1: Join to Range

GeneR — Tue, 20 Dec 2016 17:20:13 GMT

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

Gene Rinas

Re: Challenge #1: Join to Range

mceleavey — Wed, 25 Jan 2017 10:03:30 GMT

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

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.

Re: Challenge #1: Join to Range

Max06270 — Thu, 09 Feb 2017 20:25:14 GMT

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

Re: Challenge #1: Join to Range

Max06270 — Thu, 09 Feb 2017 20:25:19 GMT

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

Re: Challenge #1: Join to Range

joshuaburkhow — Tue, 14 Feb 2017 22:22:31 GMT

Nice and simple! Love it

Re: Challenge #1: Join to Range

KatieA — Fri, 24 Feb 2017 03:31:20 GMT

Attached my solution.

Re: Challenge #1: Join to Range

SeanAdams — Sun, 26 Feb 2017 18:14:56 GMT

Thank you.

3 differences in this version vs. the supplied solution:

- Summarized the client file to minimize the data as soon as possible

- Summarized the client data using distinct by Client ID to avoid double counting (you never know when you may have duplicates)

- Added in a simple tester at the end (joining on all fields) to make sure that every row was an accurate copy of expectation.

Re: Challenge #1: Join to Range

MarqueeCrew — Fri, 03 Mar 2017 16:00:31 GMT

Solution via append fields

Re: Challenge #1: Join to Range

JoshK_dup_167 — Fri, 03 Mar 2017 16:13:25 GMT

Better late than never, right?

Re: Challenge #1: Join to Range

Tharashasank — Wed, 15 Mar 2017 17:15:27 GMT

Hi ,

When i going through community i found this wonderful weekly exercise and started playing around. I just did my first challenge. It is a good example to show the usefulness of generate rows tool.

Loved Doing this exercise. I will try to do the rest of the exercises.

Thanks,

Tharashasnak

Re: Challenge #1: Join to Range

NicoleJohnson — Wed, 05 Apr 2017 03:56:11 GMT

My solution. Figured it was time to start back at #1, see all the fun i missed from back in 2015. :)

Re: Challenge #1: Join to Range

kurtwz — Wed, 05 Apr 2017 20:35:31 GMT

Hi, I'm having an issue with the Generate Rows tool. I think it's set correctly, but I'm getting the "Parse Error at char(0): Unknown variable "RowCount" error. Any suggestions for how to fix, my good sirs/ma'ams? Thank you, Kurt

Re: Challenge #1: Join to Range

kurtwz — Wed, 05 Apr 2017 20:40:30 GMT

Never mind--I didn't see the Loop Expression box at the bottom left of the Multiple Rows tool. Got it now. Thanks!