community
cancel
Showing results for 
Search instead for 
Did you mean: 
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.
Getting started with Designer? | Start your journey with our new Learning Path!

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

Community Data Engineer
Community Data Engineer

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”)!

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

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!).

Nebula
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

Alteryx Partner

Nice one! Never used the Field Info Tool before

Spoiler
wc72.PNG

 

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
Alteryx Certified Partner

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

Spoiler
Info field.PNG

 

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.

Alteryx Certified Partner

You are correct! Well spotted.