Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Building change low - tracking row by row changes

Animanga008
8 - Asteroid

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 LevelTitleSalaryBuilding
1/1/2020AMC_0011Analyst10,000B1
2/1/2020AMC_0011Analyst10,000B1
3/1/2020AMC_0011Analyst10,000B6
4/1/2020AMC_0011Analyst10,000B6
5/1/2020AMC_0012Manager20,000E3
6/1/2020AMC_0012Manager20,000E3
1/1/2020AMC_0557CFO90,000E1
2/1/2020AMC_0557CFO90,000E1
3/1/2020AMC_0557CFO90,000E1
4/1/2020AMC_0557CFO90,000E4
5/1/2020AMC_0557CFO90,000E1
6/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,

8 REPLIES 8
Greg_Murray
12 - Quasar

Hi @Animanga008,

 

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

 

Greg_Murray_0-1603909040985.png

 

Animanga008
8 - Asteroid

@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:

 

DateEmployee IDJob LevelTitleSalaryBuilding
4/1/2020AMC_0011Analyst10,000B6
5/1/2020AMC_0012Manager20,000E3

 

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
12 - Quasar

@Animanga008

 

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

 

Greg_Murray_0-1603910938416.png

 

Animanga008
8 - Asteroid

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

 

Thank you,

ColliersOS
6 - Meteoroid

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
12 - Quasar

Hi @ColliersOS ,

 

Do you have an example or sample from the inputs?

 

Thanks,

Greg

ColliersOS
6 - Meteoroid

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
12 - Quasar

@ColliersOS 

 

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

 

Greg_Murray_0-1606948445618.png

 

Labels