We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

Challenge #71: Recreate SQL Joins in Alteryx

MattD
Alteryx Alumni (Retired)

The solution to last week's challenge is HERE!

 

SQL Joins.jpg

 

Today’s challenge is for all of our analysts and data scientists who used SQL in a past (or their current) lifetime! Using the schematic above, can you recreate all the SQL joins with the Alteryx Join Tool and the datasets we have provided?

 

Spoiler
Hint: this should be easier for those of you who have mastered the Union Tool.
Former Alteryx, Inc. Support Engineer, Community Data Architect, Data Scientist then Data Engineer
alex
11 - Bolide

Who's gonna join the fun this week and submit their workflows?

Natasha
9 - Comet

Thanks for a quick SQL joins recap, though I find Alteryx joins are so much more intuitive!

 

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution! And just for kicks, did it as an app! Pick your join type and it will give you the results... SOOO much more fun than writing boring old SQL queries :)

 

Spoiler
WeeklyChallenge71.JPG
SeanAdams
17 - Castor
17 - Castor

So - I sacrificed utility for prettiness <shameful grin> and there's a few components in there that aren't needed but gave me a nice symmetry to the whole thing, and gave me a place to hang a label.

Oh, and just to add insult to injury, in making this nice and symmetrical, it also becomes less readable than @alex and @Natasha 's solutions because all the inbound connectors overlap.

 

<grin> yes - pretty silly trade-off, I know (less readable, less efficient, but easier on the eye)

 

Spoiler

2017-05-23_8-07-59.png

I've packaged my workflow along with a simple macro that allows you to check your results with very little effort

 

Have a good week all - 4 more to go!

 

Sean

 

 

SeanAdams
17 - Castor
17 - Castor

PS - for extra credit @NicoleJohnson - perhaps worth trying this with detour components or Dynamic Replace (thanks to @Joe_Mako for challenging me to learn this one).

 

If you're going to over-achieve, why not push the boat out ALL the way?   :-)

estherb47
15 - Aurora
15 - Aurora

Oh my, lots of browses to show the different joins. Much simpler than SQL!!

 

Spoiler
Spoiler
image.png
NicoleJohnson
ACE Emeritus
ACE Emeritus

@SeanAdams I actually tried Detours first, but kept detouring myself into errors... :) I will definitely give it a go again though! Can't guarantee it will be as pretty as yours, however... what a masterpiece! We'll just call you Vincent van Alteryx ;)

Simona
7 - Meteor

Pretty nice exercise, I found it quite easy but it was a good refresh for the wording :)

Attached my solution :)

Spoiler
workflow_week71.PNG
MarqueeCrew
20 - Arcturus
20 - Arcturus

Sorry, but my dog ate my homework.

 

No pretty pictures here today....

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.