Comparing lots of columns in a smart way

[JW]

I can think of how to do this really inefficiently, pretty easily, but I am sure there's a "smart" way to do this and I am curious to get some pointers because I'd like to build something sustainable and learn more about alteryx in the process.

Here is the basic scenario:

I've got a table with data that loads every day. At the beginning of each month, I want to select the data for today, as well as the data for the 1st of last month. There's a unique ID column that can join last month and this month's data together. I then want to check many (not all) of the columns for changes. The final output would be: the unique ID column, an indicator if the unique ID is new (wasn't in last month's data) or removed, and then the last month and current month version of each column that was checked for changes, and an indicator flag if there was a change to the data from this month to last month. 

Not every column in the source table would be checked for changes. I would have a list of these columns (I'll call them "validation columns") somewhere, maybe in a text table. This may be a large number of columns, hence the reason to want to do this in a "smart" way

I basically want to compare each of the validation columns from this month to last month, and flag if they had a change. 

So I could do this now if I: select this month's data (easy), select last month's data (easy), join on my unique ID column (easy), and then create MANY calculations to compare each validation column (tedious, pain to maintain later) from last month to this month. 

I am wondering how to approach this in a smart way, like how can I feed alteryx the list of my validation columns and have it compare them for changes and create all the "flag" columns to indicate changes without me having to go create and maintain a separate calculation for each column I want to check?

[DavidSkaife]

Hi @JW 

If i understand your request it's similar to something i'm working on - a validation app that compares and validates data held between two sources, and wanting to do it dynamically.

While i can't share the workflow i can outline the theory behind it, which you can adapt to your use case?

Store the Validation Columns list in a table (or a flat file, and import it), and filter them prior to the validation itself. Secondly, and this is the part that should make it 'smart', Transpose the data so your key column is the Unique ID and the rest are data columns - do this for both current and historic data seperately. You should then be able to join the two streams together using Unique ID (and another other fields as required) and any data that falls out of the left or right sides of the join should be your new and removed columns.

Transposing the data into a vertical alignment means you don't need to worry about the number of columns, or writing a large amount of formula to check.

A screenshot of the main part of my app is below for a bit of visual guidance on how i'm doing it:

Hope this helps a little!

[SPetrie]

I second @DavidSkaife on this. I also have a very similar process for my finance team (also cant share the flow) but do basically the same thing. Transposing the data is the easiest and most flexible way I can think of.