Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jarokall
Contributor II
Contributor II

How join the calendar date in a proper way

Hello,

I have generated a calendar in a DB... All dates with weekend and holiday flags.

I try to figure out the proper way to join my Calendar date with the date from the clocking system. The clocking table has only working dates, so when I join it together, I lost the weekend and holiday dates. 

I need to table all dates for each ID from the clocking table with data or nulls and then do some scripts/calculations on top of that like attendance (was or was not at work), etc.

Have you please idea how to do it in a proper and fast way?

Thanks

[Calendar]:
LOAD
    `calendar_date`,
    date_stamp,
    `calendar_day`,
    `calendar_work`,
    `calendar_holiday`,
    `calendar_name`;
SQL SELECT 
    `calendar_date`,
    `calendar_date` as date_stamp,
    `calendar_day`,
    `calendar_work`,
    `calendar_holiday`,
    `calendar_name`
FROM calendar.calendar as calendar;
 
join(Calendar)
LOAD 
date_stamp,
    user_id,
    total_time as working_shift,
    ;
SQL SELECT 
    rsch.date_stamp,
    rsch.user_id,
    rsch.total_time / 3600 as total_time
FROM clocking.recurring_schedule as rsch
;
Labels (1)
3 Replies
Vinay_B
Support
Support

Hi @jarokall Can you try the below and let me know how it goes?

[Calendar]:
LOAD
`calendar_date`,
date_stamp,
`calendar_day`,
`calendar_work`,
`calendar_holiday`,
`calendar_name`;
SQL SELECT
`calendar_date`,
`calendar_date` as date_stamp,
`calendar_day`,
`calendar_work`,
`calendar_holiday`,
`calendar_name`
FROM calendar.calendar as calendar;

ClockingData:
LOAD
date_stamp,
user_id,
total_time as working_shift
RESIDENT Calendar;

LEFT JOIN(Calendar)
LOAD
date_stamp,
user_id,
total_time as working_shift
FROM clocking.recurring_schedule;

Note: Make sure the field names, such as calendar_date, date_stamp, user_id, and total_time, match the field names in your actual data schema.

If this resolves your query, please click on "Accept as Solution" for confirmation. Thanks!
jarokall
Contributor II
Contributor II
Author

Hi, @Vinay_B 

thanks for the response. I tried your solution but I get this error.

The following error occurred:
Field 'user_id' not found
 
The error occurred here:
ClockingData: LOAD date_stamp, user_id, total_time as working_shift RESIDENT Calendar
 
I am sure that the field names match the names in the data schema, but the issue here is probably that I don't have the user_id in the calendar?
 
Have you please another idea of how I get a table with all dates from the calendar for all user_id?
 
I tried some concat, and cross joins but everything was heavy or didn't work. Need some easy and lightweight solution. 
 
Maybe I didn't need to use the whole calendar because I need only a work week without the weekends but when I used only the date stamp from clocking, there are no public holiday dates so when I calculate the workload on a week where is a public holiday, I missed some days and workload percentages are wrong.
 
Thanks for your help.
SunilChauhan
Champion II
Champion II

recurring_schedule:
LOAD 
Date(date_stamp,'DD/MM/YYYY') as calendar_date,
date_stamp,
    user_id,
    total_time as working_shift,
    ;
SQL SELECT 
    rsch.date_stamp,
    rsch.user_id,
    rsch.total_time / 3600 as total_time
FROM clocking.recurring_schedule as rsch
;
 
 
 
 
[Calendar]:
Left Join
LOAD
    `calendar_date`,
    date_stamp,
    `calendar_day`,
    `calendar_work`,
    `calendar_holiday`,
    `calendar_name`;
SQL SELECT 
    `calendar_date`,
    `calendar_date` as date_stamp,
    `calendar_day`,
    `calendar_work`,
    `calendar_holiday`,
    `calendar_name`
FROM calendar.calendar as calendar;
 
hope this helps
Sunil Chauhan