Weekly Challenge

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

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

Submit Feedback
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.

Challenge #72: Compare Data Sets (Fields/Columns)

Highlighted
Alteryx Community Team
Alteryx Community Team

The solution to last week's challenge is HERE!

 

Challenge 72.jpg

 

This week’s challenge is inspired by @SeanAdamsmastery of the Field Info Tool! Can you follow in his footsteps to master the tool and use it in building a workflow that can compare two incoming data sets to see if they contain the same columns? Be sure to be sensitive to string case and any columns that have underscores substituted for spaces (i.e. “Store name” should match to “store_name”)!

Highlighted
Alteryx Certified Partner

Thanks for the new challenge! 

 

 

Spoiler
My initial solution was to get field names through transpose and summarise, but this was a perfect chance to try Field Info tool which I've never used before!

Screen Shot 2017-05-30 at 22.12.46.png

Highlighted
Alteryx Certified Partner

Very similar to Natasha's. Wish I would have thought to use Join Multiple for the join (would have cut out 3 tools!).

Highlighted
16 - Nebula
16 - Nebula

Thank you @MattD,   Solution is a bit different from others, I built a macro a few weeks ago to compare column headers (with a bunch of options for case, column ordering, type etc), and then compare the rows in each column too.    It produces 3 outputs, one of which is a report format output.   I know that reusing work that was done a few weeks ago is probably a cheat, so I'll apologise up front.    

 

I hope this macro is helpful for folk - it's saved me tonnes of time on the weekly challenges, comparing my results to those of @MattD and @JoeM

(more info here: https://community.alteryx.com/t5/Alteryx-Product-Ideas/Tool-to-compare-2-data-sets/idi-p/62520#M3950...

 

Spoiler

Main flow:

2017-05-31_8-59-19.png

 


Configuration: 
2017-05-31_8-59-47.png


Report output: 

2017-05-31_9-00-22.png

Macro (it's too big to put in an image in full resolution, so I shrunk it down to give you a sense of it - detail is in the attached macro)
2017-05-31_9-00-51.png


3 more to go to the big 75 - and it looks like @LordNeilLord is going to be very close to 75 in the next 2-3 weeks, so it may be a 4 way sprint to the final badge!

 

@MattD @JoeM @LeahK - you may have to cook up a badge for 100 challenges completed in a few weeks  :-)

Highlighted
Alteryx Certified Partner

@SeanAdams that's a very useful macro! Thanks for sharing it here

Highlighted
Alteryx Partner

Nice one! Never used the Field Info Tool before

Spoiler
wc72.PNG

 

Highlighted
Alteryx Certified Partner

Fairly straightforward, and a great illustration of the tool.

 

Spoiler
I fed the two inputs into the Field Info tool, which effectively streams out the field names as rows. I then created a key field which took the Name field and converted to a common uppercase field and removed the underscores. I then joined the two streams on this key using the Join Multiple tool, specifically because it allows you to include unjoined fields in the output. I then simply dropped the unwanted columns.
I think this is pretty much exactly the same as others have done it. I assume most would do the same but with a union on the join, but it's just an illustration of the nice functionality on the Join Multiple tool.

Solution.PNGResults.PNG
Highlighted
Alteryx Certified Partner

Nice challenge, I never used the field info tool before :)

Spoiler
Info field.PNG

 

Highlighted
Alteryx Certified Partner

@mceleavey Your solution is very similar to mine. By the way, you don't need a Select tool at the end as you can do it all in Join Multiple tool.

Highlighted
Alteryx Certified Partner

You are correct! Well spotted.