Create table of date with roster hours - based on roster template and startdate

[Ted2]

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!

[jcmachado]

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