Building change low - tracking row by row changes

[Animanga008]

Hi all,

I am trying to build a change log on our employee data. Currently, I can do it via a brute method. That is, I use the multi-row tool and manually write a logic to scan a field, row-by-row. If I have 10 fields that I am interested in, I will have 10 multi-row tools looking at the data.

My dataset has 100+ fields that I need to scan for changes.

Is there a way to do this smarter? Can Alteryx not only identify a change between rows but also identify the field without me manually writing out a logic for each individual field?

An example of my data:

Date	Employee ID	Job Level	Title	Salary	Building
1/1/2020	AMC_001	1	Analyst	10,000	B1
2/1/2020	AMC_001	1	Analyst	10,000	B1
3/1/2020	AMC_001	1	Analyst	10,000	B6
4/1/2020	AMC_001	1	Analyst	10,000	B6
5/1/2020	AMC_001	2	Manager	20,000	E3
6/1/2020	AMC_001	2	Manager	20,000	E3
1/1/2020	AMC_055	7	CFO	90,000	E1
2/1/2020	AMC_055	7	CFO	90,000	E1
3/1/2020	AMC_055	7	CFO	90,000	E1
4/1/2020	AMC_055	7	CFO	90,000	E4
5/1/2020	AMC_055	7	CFO	90,000	E1
6/1/2020	AMC_055	7	CEO	100,000	E1

An example of my logic in the multi-row tool:

Tool 1: if [Row-1:Employee ID]=[Employee ID] then (if [Row-1:Job Level] != [Job Level] then 'JOB Change: '+[Row-1:Job Level]+", "+ [Job Level] else "" endif) else "" endif

Tool 1 Output (for some rows): JOB Change: 1, 2

Tool 2: if [Row-1:Employee ID]=[Employee ID] then (if [Row-1:Title] != [Title] then 'TITLE Change: '+[Row-1:Title]+", "+ [Title] else "" endif) else "" endif

Tool 2 Output (for some rows): TITLE Change: Analyst, Manager

Tool XYZ...etc: if [Row-1:Employee ID]=[Employee ID] then...etc

Thanks,

[Greg_Murray]

Hi @Animanga008,

You can use the MD5 function and the multi-row formula to check for changes across all of the fields. 

[Animanga008]

@Greg_Murray , interesting alternative!

But effectively, would MD5 be any different than running my rows through a Unique tool? Both can identify that a row is different, both can be further enriched and clarified by adding some identifier columns through a Formula tool...

But critically, I need to know which fields are different in rows that are different from their prior entry.

For instance:

Date	Employee ID	Job Level	Title	Salary	Building
4/1/2020	AMC_001	1	Analyst	10,000	B6
5/1/2020	AMC_001	2	Manager	20,000	E3

Once the logic hits these two entries in the dataset, I would like to generate an output that explicitly returns information like the following:

• There was a job change and that change is 2 from 1
• There was a title change and that change is Manager from Analyst
• There was a salary change and that change is 20k from 10k...yadda yadda yadda

Using Unique, or MD5, or multi-row can all identify which row is different, but I'm not sure how to take that next step and also bring back the value of what's changed.

[Greg_Murray · Accepted solution]

@Animanga008, 

Sorry I think I missed the second part of what you were looking for. This should cover it. 

[Animanga008]

@Greg_Murray , your workflow looks perfect and solves exactly what I'm looking for.

Thank you,

[ColliersOS]

I came across this solution as it is almost identical to a problem I'm trying to solve as well. However, I'm looking to compare two different tables, one of which is an alteryx database (new) and the other which is a SQL table (old). 

how would the workflow function to compare two different sources, but enable the same kind of output you provided for this solution? 

[Greg_Murray]

Hi @ColliersOS ,

Do you have an example or sample from the inputs?

Thanks,

Greg

[ColliersOS]

Here's a very small sample of just two text inputs that will mirror the alteryx database and the sql table, but the number of fields I'd track would be around 4-5. But this should be a decent enough start conceptually with the date and the amount. 

[Greg_Murray]

@ColliersOS 

Something like this should work. You may have to do some tweaking to get it work with your actual use case.