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: DateEmployee IDJob LevelTitleSalaryBuilding1/1/2020AMC_0011Analyst10,000B12/1/2020AMC_0011Analyst10,000B13/1/2020AMC_0011Analyst10,000B64/1/2020AMC_0011Analyst10,000B65/1/2020AMC_0012Manager20,000E36/1/2020AMC_0012Manager20,000E31/1/2020AMC_0557CFO90,000E12/1/2020AMC_0557CFO90,000E13/1/2020AMC_0557CFO90,000E14/1/2020AMC_0557CFO90,000E45/1/2020AMC_0557CFO90,000E16/1/2020AMC_0557CEO100,000E1 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,

Building change low - tracking row by row changes

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,

Help

Accepted answers

Greg_Murray

@Animanga008,

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

MD5 ex.yxmd

All comments

Greg_Murray

Hi @Animanga008,

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

MD5 ex.yxmd

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

@Animanga008,

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

MD5 ex.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703