Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ted2
Contributor
Contributor

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

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!

Labels (1)
1 Reply
jcmachado
Contributor III
Contributor III

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