I have an issue with two tables of data which contain field values for a list of items (services in this case).
The first table contains the current live values of each field, while the second table contains the historical values prior to each update.
My goal is to use the data from both tables so I can identify when changes were made and the corresponding value changes.
Below are examples of how the tables appear currently:
What I am trying to get is a view where I can map the historical fields with the corresponding live fields (Version_ID, Service_Colour, Date).
My goal is to then be able to use a global calendar to filter for colour changes over periods of time. I.e. Identify the previous Service_Colour for each Service_ID and the date on which it was implemented.
I had tried to do this is a different way using mapping but did not get the required output.
Does anyone have an idea on the most effective way to do this?
You can connect the two tables via the SERVICE_ID field but then you will have to use an aggregation function in the expressions that involve the Historical_Data table such as Max(HistDate) as an example. Or you create a concatenated field that will serve as the link between the two tables with is comprised of Service_ID & '-' & HistDate.
1st thing I will do is to rename HistDate as Date so that when you concatenate you have 1 field,and have extra field for both tables, 0 AS Source_Table in Live table and 1 AS Source_Table in Historical table.
To create your Master Calendar just take the Min and Max of Date field and build your master calendar from this 2 fields.
You can then use Set Analysis to differentiate between Historical Data and Live Data in the chart.