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

[MattD]

The solution to last week's challenge is HERE!

This week’s challenge is inspired by @SeanAdams’ mastery 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”)!

[Natasha]

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!

[Garrett]

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

[SeanAdams]

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:



Configuration: 



Report output: 



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)

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]

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

[jacob]

Nice one! Never used the Field Info Tool before

Spoiler

[mceleavey]

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.

[Simona]

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

Spoiler

[Natasha]

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

You are correct! Well spotted.