shift hours

[tomcatxx]

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?

[mtucholski]

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.

[tomcatxx]

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;

[mtucholski · Accepted solution]

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:
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,

clock_time
Resident Clock_Time;

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