Forum Discussion
Temporal Data Linkage
Hi all.
Been a big fan and usage of this community over the years for solutions.
This problem has me a bit stumped and are hoping you can assist.
Will do my best to describe it.
I need to link a column from one data table to another data table, based on dates of the first table.
For example, I have a number of monthly HR reports that show staff and their department. The reports will differ overtime as staff change departments.
The second table I have is transaction data which contains the date of the transaction and the staff member.
The problem I am trying to solve is, I need to link the transaction to the department that staff member was linked to at the time of the transaction. In other words, the transaction date needs to query the corresponding HR table for that month.
Eg.
| HR Report Table - May 2022 | Staff Name | Department |
| John Doe | Department A | |
| Jane Smith | Department B |
| HR Report Table - June 2022 | John Doe | Department C |
| Jane Smith | Department B |
| Transaction Table | 15 May 2022 | John Doe | Department A |
| 15 June 2022 | John Doe | Department C |
Any suggestions how I can solve for this?
1 Reply
Hi,
- merge all the HR Report Tables into one
- In the merged HR Report Table: Add a custom column containing Month & Staff Name
- In the Transaction Table: Add a custom column containing Month & Staff Name (must be the same as the custom column in the merged HR Report Table)
- Connect the Transaction table and the HR Report Table by the newly Created Columns
BR
