New to Qlik Sense (and to Qlik in general ), so I hope someone can point me in the right direction.
I need to link 2 tables together based on a date range. To be straight forward; table 1 contains the scheduled hours for a employee with a start-to-end date range. table 2 contains the actual clock date with the hours worked for that one date.
For example employee 2b; is scheduled for 8hrs a day between 01/01/2019 and 10/01/2019 (table1). The actual clocking on 05/01/2019 is 8hrs (table2).
In the end the result we are looking for is to see if employees are working less or more than scheduled. How do I lookup the clocked date in table2 in the date range provided by table1?
Table1
Holds per date-range the expected hours someone should work.
Employee | startDate | EndDate | Hours |
1A | 01/01/2019 | 05/01/2019 | 8 |
1A | 06/01/2019 | 10/01/2019 | 4 |
2b | 01/01/2019 | 10/01/2019 | 8 |
3c | 31/07/2018 | 31/12/2020 | 7 |
Table2
Holds per specific day the amount of hours someone actually worked
Employee | TheDate | Worked |
1A | 05/01/2019 | 8 |
2b | 05/01/2019 | 8 |
3c | 05/01/2019 | 8 |
1A | 06/01/2019 | 3.5 |
2b | 06/01/2019 | 8 |
3c | 06/01/2019 | 6 |
