Challenge #1: Join to Range

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.

challenge_1_start_file.yxmd

challenge_1_solution.yxmd

Parse

Join

Preparation

Intermediate

Data Preparation

Core

Transform

Accepted answers

All comments

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.

challenge_1_solution.yxmd

Max06270

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

Spoiler

challenge_1_MK.yxmd

Max06270

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

Spoiler

challenge_1_MK.yxmd

joshuaburkhow

Nice and simple! Love it

KatieA

Attached my solution.

KA_challenge_1.yxmd

SeanAdams

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.

challenge_1_SeanSolution.yxmd

MarqueeCrew

Solution via append fields

challenge_1_MarqueeCrew.yxmd

JoshK_dup_167

Better late than never, right?

challenge_1_my_solution.yxmd

Tharashasank

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

NicoleJohnson

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

Spoiler

challenge_1_NicoleJohnson.yxmd

kurtwz

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

kurtwz

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

mceleavey

I know, it's really addictive.

I Alteryx socially. I can stop whenever I want...

Laurap1228

First things first! I am going through the weekly challenges in order. Here is my solution to challenge #1

Spoiler

challenge_1_LP.yxmd

Anurag

Nice One!!

I used Formula tool to create a new column of Region in the first input table, and then joined both the table based on the Primary Key Column "Region". After that I used Summarize tool to Group by and count as per the desired output.

challenge_1_AP.yxmd

SeanAdams

Hey Anurag - welcome to the community and the weekly challenges!

Natasha

My solution before I learned about Generate Rows tool.

challenge_1_NK.yxmd

estherb47

Spoiler

challenge_1_EHB_solution.yxmd

SeanAdams

welcome @Inactive User - great to see you tackling the challenges.

Never too late!

Treyson

I'm late to this party but I am going to catch up! Love that there is a way to get in front of problems that I don't see where I work!

Challange 1 Treyson Marks.yxmd

Treyson

Woah I was looking at the create record tool and that's way better! I am going to use this all the time!

Challange 1 Treyson Marks 2.yxmd

Treyson

Woah I was looking at the create record tool and that's way better! I am going to use this all the time!

Challange 1 Treyson Marks 2.yxmd

MarvinPinto

Used a slightly different approach to resolve the challenge by using the Tile tool to create groups

WC1_Join to range.yxmd

SeanAdams

Welcome Marvin - great to see you on the community.
Feel free to post any questions you may have here: https://community.alteryx.com/t5/Data-Preparation-Blending/bd-p/data-preparation-blending
... or any ideas you have for new product features here: https://community.alteryx.com/t5/Alteryx-Product-Ideas/idb-p/product-ideas

Looking forward to seeing you hit the "5 challenges complete" badge - with today's work you should already have 2 or 3 badges in the bag!

DE0413

I'm fairly new to Alteryx but want to take advantage of the learning experience. Attached is my solution to the week 1 challenge and curious to know your thoughts on this approach.

Thank you,

Denise

AlteryxWeeklyChallengeWeek1.yxmd

ydmuley

I just loved these challenges, and as someone mentioned before on this thread, it is very addictive.

Attaching my solution for the first weekly challenge. Many more to come.

Thanks,

Yug

challenge_1_start_file_Solution.yxmd

Phil_L57

Hi GeneR,

I also used the Generate Rows technique. With the generate rows how come the field that is being created doesn't need to be enclosed in square brackets? It does also work with the brackets included though.

Thanks

Phil

challenge_1_start_file vPL.yxmd

LordNeilLord

A nice easy on to start with

Spoiler

raj_k

Solution for Challenge # 1

AC 1 - Solution_Raj.yxmd

LordNeilLord

Workflow

challenge_1_LNL.yxmd

irisTZ

Solution for Challenge #1

challenge_1_with Solution.yxmd

SeanAdams

Hey @irisTZ

Just wanted to say Welcome, and congratulations on your first solution posted. I've personally found the weekly challenges to be one of the MOST valuable learning tools that I've come across for the alteryx product, and I hope that you'll find the same!

Good luck, and welcome to the journey. There's loads of fun ones ahead, so you've got a lot to look forward to.

KOBoyle

Solution attached. I too used functions within the Generate Rows tool to parse the start and end postal codes required for the expressions.

Ken

challenge_1_solution_KO.yxmd

JORGE4900

This is a super creative solution @MarvinPinto. I enjoyed looking at it.

JORGE4900

Hi Denise,

Your approach didn't seem that "fairly new". That was a pretty outside the box approach; I like how you use a Cartesian product by appending the two tables together and filter the data again, creating a cross join basically.

That was very creative @DEO413

Suzanne

Learned a lot. I knew how to do this in Access but was stumped in Alteryx. The generate rows tool is really neat. Thank you.

challenge_1_Suzanne.yxmd

Pasty

Finished

challenge_1_finished（join，一个数match一个range）.yxmd

Good challenge to get started with. I used a formula after appending the fields instead of doing it the generate rows way but I'll now try it that way too as it's all about building knowledge!

Alteryx Challenge 1 My Solution.yxmd

Samanthaj_hughes

arh, I started by doing this! then I thought but there is not 1000s of range options so a formula tool would be quicker. Love that there are many ways to solve the same problem.

nbandla

First Ever Weekly Challenge Completed

Weekly Completed Challenge 1.yxmd

KeithTroutt

challenge_1_solution_keithtroutt.yxmd

NigelW

Nice to see error checking

JMX

Had to look off of everyone else to understand the recreate postal code area

Challenge_1_JMX.yxmd

MikeLR

I like the idea of the tester join at the end to check the results.

Cndro_Consulting

check out my solution and thoughts

Join to range.yxmd

John_

Interesting way to do it without using generate rows.

tirpower

Cool,

Didn't think of using a Tile tool immediately - chose an Append tool (which if this list were larger, then it probably wouldn't be a sustainable tool to use) coupled with a Formula to designate postal codes in a given range, then Filtered to keep the correct ones, finally joining on the range.