Building change low - tracking row by row changes
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Animanga008,
You can use the MD5 function and the multi-row formula to check for changes across all of the fields.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry I think I missed the second part of what you were looking for. This should cover it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Something like this should work. You may have to do some tweaking to get it work with your actual use case.
