Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a report to analyse worked hours compared to an employee’s assigned roster per day.
Therefore I need to create a table with the amount of hours per date according to the assigned roster
I have the following tables:
ROSTERSETTINGS
Employee_id
Roster_id
Startdate_roster
Enddate_roster
ROSTERHOURS
Roster_id
Roster_name
Roster_days
Roster_day_sequencenumber
Roster_hours
ROSTERSETTINGS EXAMPLE:
Employee_id |
Roster_id |
Startdate |
Enddate |
Startday |
999 |
123 |
1-12-2022 |
31-12-2099 |
4 |
ROSTERHOURS EXAMPLE:
Roster_id |
Roster_name |
Roster_day |
Roster_day_sequencenumber |
Roster_hours |
123 |
Roster 40 |
Mon |
1 |
8 |
123 |
Roster 40 |
Tue |
2 |
8 |
123 |
Roster 40 |
Wed |
3 |
6 |
123 |
Roster 40 |
Thu |
4 |
8 |
123 |
Roster 40 |
Fri |
5 |
6 |
123 |
Roster 40 |
Sat |
6 |
0 |
123 |
Roster 40 |
Sun |
7 |
0 |
The employee started on 1-12-2022, this is the 4th day of the assigned roster, therefore the “startday” of the ROSTERSETTINGS record is “4”.
The table I want to create is the following:
Date | Roster_day_sequencenumber | Roster_hours | Roster_id | Employee_id |
1-12-2022 | 4 | 8 | 123 | 999 |
2-12-2022 | 5 | 6 | 123 | 999 |
3-12-2022 | 6 | 0 | 123 | 999 |
4-12-2022 | 7 | 0 | 123 | 999 |
5-12-2022 | 1 | 8 | 123 | 999 |
6-12-2022 | 2 | 8 | 123 | 999 |
7-12-2022 | 3 | 6 | 123 | 999 |
8-12-2022 | 4 | 8 | 123 | 999 |
9-12-2022 | 5 | 6 | 123 | 999 |
10-12-2022 | 6 | 0 | 123 | 999 |
11-12-2022 | 7 | 0 | 123 | 999 |
Many thanks in advance for your help!
You can create this table by joining the ROSTERSETTINGS and ROSTERHOURS tables based on Roster_id and then using a loop to generate the dates starting from the Startdate_roster and the Startday.
Here's an example of how you can do this in QlikView script:
// Load ROSTERSETTINGS table
LOAD
Employee_id,
Roster_id,
Startdate_roster,
Enddate_roster,
Startday
FROM
ROSTERSETTINGS.qvd (qvd);
// Load ROSTERHOURS table
LOAD
Roster_id,
Roster_name,
Roster_day,
Roster_day_sequencenumber,
Roster_hours
FROM
ROSTERHOURS.qvd (qvd);
// Create a new table with Date, Roster_day_sequencenumber, Roster_hours, Roster_id, and Employee_id
ROSTER:
LOAD
date(date#(Startdate_roster, 'DD-MM-YYYY') + Roster_day_sequencenumber - Startday) as Date,
Roster_day_sequencenumber,
Roster_hours,
Roster_id,
Employee_id
RESIDENT
ROSTERSETTINGS
JOIN
ROSTERHOURS
ON
ROSTERS