Hours generated

[Sunflower_00]

Hi I have created the start time and end time in the load editor script. So I have the min(start time) , min(starttime + 30) in this format (DD/MM/YYYY hh:mm:ss) and the number of hours. I want to know how many hours each ID worked. I tried interval(starttime - endtime, 'hh') and it doesn't give me the right answer.

[cristianj23a]

Hi Sunflower,
try this code.

// Sample data for WorkLog table
WorkLogTest1:
LOAD * INLINE [
ID, StartTime, EndTime, HoursWorked
1, '28/07/2023 08:00:00', '28/07/2023 10:30:00',
2, '28/07/2023 09:15:00', '28/07/2023 12:45:00',
3, '28/07/2023 13:30:00', '28/07/2023 17:00:00'
];

// Convert StartTime and EndTime to QlikView timestamp format
WorkLogTest2:
LOAD
ID,
Time#(StartTime, 'DD/MM/YYYY hh:mm:ss') as StartTime,
Time#(EndTime, 'DD/MM/YYYY hh:mm:ss') as EndTime
RESIDENT WorkLogTest1;

// Calculate HoursWorked using Interval function
NoConcatenate
WorkLog:
LOAD
ID,
StartTime,
EndTime,
Interval(EndTime - StartTime, 'hh:mm') as HoursWorked
RESIDENT WorkLogTest2;
Drop Table WorkLogTest1,WorkLogTest2;

Regarts.