Forum Discussion
Create relationship between two tables with only most recent data
Hi,
I have a large table with HR data that is updated each month so we can complete trend reporting.
A simplified version:
I also have a fact second table that lists all current employees (has their UserID) and their annual leave.
How do I link the leave table to only the most recent report month data in the main table? Since we we want to slice by the most recent Job Family and Career Step. So in the example above I'd want it to connect only to the rows for 1/03/2023, but once the April data is loaded I'd want it to link to those rows.
Any help greatly appreciated!
Best ,
Soph
Hi,
I'd look at it a different way.
Create an employee dimension (May just br your existing table if it has a row per employee).
Create a 1 to many relationship from employee table to your main fact table.
Create a date table and link that to your main fact table.
Then when you filter on April you only see the related rows for April.
3 Replies
- bcdobbs
Community Champion
Hi,
I'd look at it a different way.
Create an employee dimension (May just br your existing table if it has a row per employee).
Create a 1 to many relationship from employee table to your main fact table.
Create a date table and link that to your main fact table.
Then when you filter on April you only see the related rows for April.
Hi Ben,
Thanks for responding - unfortunately both tables have multiple rows per employee since the main table (the example) is run every month so if people are still employed they will appear again, and the leave table is a fact table since an employee will have multiple periods of annual leave.
Do you know how i might create an employee dim from the main table that only pulls data from the latest report month?
Best,
Soph
- bcdobbs
Can you send over some dummy data for your employee table? I'll mock up an example for you.