Preventing historical data being overwritten

[James_Simpson_125]

Hello,

I am currently creating a workflow that takes over a year of order information and joins the orders with an external table, that has the names associated to who should have created the order/who's work it should be. 

Example: Order 12345678 comes out of Planner John Doe's assigned work area, code #0999.

I'm currently joining the planner's name to the order through the code, in this case #0999.

The issue I'm running into is that if John Doe is re-assigned to a different code (#0998), decides to find a new job, gets promoted or fired- I need to re-assign his work area of #0999 to the new planner's name, Leonardo DiCaprio, which then assigns Leonardo DiCaprio to all Historical orders that should be John Doe's. 

I am curious if there is a tool/work around that allows for dynamic changes to these names to preserve historical data, or if I have to resort to writing a formula for every planner change, that is something along the lines of IF [planner name] = 'John Doe' and [Created date]>'12/1/22' Then "Leonardo DiCaprio else [planner name] endif 

[Felipe_Ribeir0 · Accepted solution]

Hi @James_Simpson_125 

There is a database concept called slowly changing dimensions (if you search for it you will find a bunch of material), that is used to keep historical track of dimensions, exactly like you need.

I dont know if you are using a database and how your data looks like, but this would solve your problem.

Example: you have a fact table with transactions per date and you want to analyse these transactions per client home state. The problem is, the home state of your customers change over time.

Because of that, you need to have a dimension table that contains this information (StartDate and EndDate of each address State for each CustomerID), so you will know how to analyse this through time. If you have a database admin, he will know how to build a table like this (the EndDate of the second row is null means that the information still valid until today):

Having a table like this, you can easily have the information considering the address change over time using a join and a filter:

I wrote too much, but this is the usual way to approach this type of problem. Hope this is useful.

[James_Simpson_125]

Thanks for the reply, Felipe. So, I'm looking at the attached workflow you presented, and I think this works for me. It looks like I will need to add start and end dates to the planner table, and when a change occurs to a planner, I will need to append the table to include the new person as well:

For Example:

0999 John Doe 1/1/22 to 12/1/22

0999 Leonardo Dicaprio 12/2/22 - null (the null is explained in your workflow - wanted to mention I understood that part). 

From there the data is joined and the duplicates are removed

example

order 0555555 will have two line-items, one with John, one with Leonardo, but because of the date filter, filtering for only the ones where the date is between the duration of employment (start and end date) it will pull the active worker. 

[Felipe_Ribeir0]

@James_Simpson_125 , yes, if you can built this table with start and end dates, you will be able to use the same logic!

[James_Simpson_125]

Perfect, Thanks Felipe!