topic Re: shift hours in QlikView

shift hours

tomcatxx — Wed, 25 Nov 2020 16:16:04 GMT

Hi there. I want to change colock time into shift time 8 hours.

06:00:00 to 13:59:59 --------------> 00:00:00 to 07:59:59

14:00:00 to 21:59:59 --------------> 00:00:00 to 07:59:59

and

00:00:00 to 05:59:59 ----------------> 00:00:00 to 05:59:59

22:00:00 to 23:59:59 ----------------> 06:00:00 to 07:59:59

That means for morning shift, just let ClockTime - 6 hours

for afternoon shift just let ClockTime - 14 hours

and so on.

For example

Clock Time	Shift Time
08:02:52	02:02:52
13:35:21	07:35:21
19:22:45	05:22:45
23:11:54	01:11:54

What is the LOAD script with IF?

Re: shift hours

mtucholski — Fri, 27 Jul 2018 11:42:02 GMT

Hi,

That's what you have asked for:

if(clock_time>='06:00:00' and clock_time<'22:00:00',if(clock_time>='14:00:00',clock_time-14/24,clock_time-6/24),if(clock_time>='22:00:00',clock_time-16/24,clock_time)) as shift_time

but personally I think that this one would be more appropriate:

if(clock_time>='06:00:00' and clock_time<'22:00:00',if(clock_time>='14:00:00',clock_time-14/24,clock_time-6/24),if(clock_time>='22:00:00',clock_time-22/24,clock_time+2/24)) as shift_time

Try both, and let me know what you think.

Re: shift hours

tomcatxx — Fri, 27 Jul 2018 12:04:47 GMT

Thank you. It lokks not bad. I made a test, but there is a Problem.

Clock_Time:
LOAD * inline
[clock_time
02:22:11
23:21:56
21:20:48];

Schicht_Zeit:
Load
if(clock_time>='06:00:00' and clock_time<'22:00:00',
if(clock_time>='14:00:00',clock_time-14/24,clock_time-6/24),
if(clock_time>='22:00:00',clock_time-22/24,clock_time+2/24)) as shift_time
Resident Clock_Time;

Re: shift hours

mtucholski — Fri, 27 Jul 2018 12:09:34 GMT

You have just created two tables without connecton. There have to be two fields with the same name in different tables so Qlik will now by which field it should connect those tables.

Try this:

clock_time
Resident Clock_Time;

Press "CTL+T" to look how your tables looked with your and with my code. You will get it.