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,
Solved! Go to Solution.
Hi @Animanga008,
You can use the MD5 function and the multi-row formula to check for changes across all of the fields.
@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:
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.
Sorry I think I missed the second part of what you were looking for. This should cover it.
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?
Something like this should work. You may have to do some tweaking to get it work with your actual use case.