Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

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

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

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

MattD
Alteryx Alumni (Retired)

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

Former Alteryx, Inc. Support Engineer, Community Data Architect, Data Scientist then Data Engineer
Natasha
9 - Comet

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

Garrett
11 - Bolide

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

SeanAdams
17 - Castor
17 - Castor

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  :-)

Natasha
9 - Comet

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

jacob
6 - Meteoroid

Nice one! Never used the Field Info Tool before

Spoiler
wc72.PNG

 

mceleavey
17 - Castor
17 - Castor

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


Bulien

Simona
7 - Meteor

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

Spoiler
Info field.PNG

 

Natasha
9 - Comet

@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.

mceleavey
17 - Castor
17 - Castor

You are correct! Well spotted.

 



Bulien