Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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;
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.