Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

leroboy1980
New Contributor

Time in shifts

Hello,

We work in shifts. Now I would like to measure the output per shift.
Which formula can I use to divide the time field (bearbzeit) into shifts.

eg.        from 06:00-14:00       shift vroege

             from 14:00-22:00       shift late

             from 22:00-06:00       shift nacht

             from 06:00-15:00       shift ISO

THX

9 Replies
brunobertels
Valued Contributor II

Re: Time in shifts

Hi

May be somethink like this in your script

if(bearbzeit<='22:00','shift late',

if(bearbzeit<='15:00','shift ISO',

if(bearbzeit<='14:00','shift vroege',

if(bearbzeit<='06:00','shift nacht')))) as shifts,


Bruno


big_dreams
Contributor III

Re: Time in shifts

if(bearbzeit<='06:00' and bearbzeit>='22:00','Shift nacht',

     if(bearbzeit<='15:00' and bearbzeit>='06:00','Shift vroege',

          if(bearbzeit<='14:00' and bearbzeit>='06:00','Shift ISO','Shift late'

           )

     )

)


Regards,

leroboy1980
New Contributor

Re: Time in shifts

thx but I get an error loading the data

leroboy1980
New Contributor

Re: Time in shifts

thx but I get an error loading the data

leroboy1980
New Contributor

Re: Time in shifts

This is the loadscript:

LIB CONNECT TO ' ?????????';
[Master]:
LOAD
    firstvalue(time(sperrzeit)) as Tijdstip,
    firstvalue(date(zeit)) as Datum,
    etikettnr,
    firstvalue(esnr) as Machine_nr,
    firstvalue(auftnr) as /*Order_nr*/key1,
    firstvalue(pos) as /*pos*/key2,
    esname as Machine,
    firstvalue(bearbzeit) as bearbzeit,
   
    if(sperrzeit<'22:00','Late',
    if(sperrzeit<'14:00','Vroege',
    if(sperrzeit<'6:00','Nacht'))) as Shifts
   

where auftnr<>0 and esnr>22 and esnr<=2930 and (esname='ISO-lijn 1 (Out)' or esname='ISO-lijn 2 (Out)' or esname='Gelaagd Lijn (Out)' or esname='Buigoven groot (uit)' or esname='Gelaagd Lijn (Out)'
or esname='Busetti F12 (Out)' or esname='Zandstralen' or esname='Lisec ESL 60/30 RS' or esname='Bottero' or esname='Lisec VSL-45' or esname='Oven (uit)'
or esname='Intermac (Out)' or esname='Waterjet (Out)' or esname='Vacuum Lagen (Out)' or esname='RBB (Out)') and len(etikettnr)=9

group by etikettnr,esname;

LET vRowCounter = NoOfRows('Master');
TRACE $(vRowCounter) rows loaded in table "Master";

SQL SELECT auftnr,

    pos,

    glasart,

    warengrp,

    produktgrp,

    breite,

    hoehe,

    flaeche,

    auftnr_pos

FROM ???????;

big_dreams
Contributor III

Re: Time in shifts

where you are creating sperrzeit????


I don't see it in your sql script.


Regards,


leroboy1980
New Contributor

Re: Time in shifts

sorry I copied the wrong script

LIB CONNECT TO '............................';
[Master]:
LOAD
    firstvalue(time(sperrzeit)) as Tijdstip,
    firstvalue(date(zeit)) as Datum,
    etikettnr,
    firstvalue(esnr) as Machine_nr,
    firstvalue(auftnr) as key1,
    firstvalue(pos) as key2,
    esname as Machine,
    firstvalue(bearbzeit) as bearbzeit,
   
    if(sperrzeit<'22:00','Late',
    if(sperrzeit<'14:00','Vroege',
    if(sperrzeit<'6:00','Nacht'))) as Shifts
   

where auftnr<>0 and esnr>22 and esnr<=2930 and (esname='ISO-lijn 1 (Out)' or esname='ISO-lijn 2 (Out)' or esname='Gelaagd Lijn (Out)' or esname='Buigoven groot (uit)' or esname='Gelaagd Lijn (Out)'
or esname='Busetti F12 (Out)' or esname='Zandstralen' or esname='Lisec ESL 60/30 RS' or esname='Bottero' or esname='Lisec VSL-45' or esname='Oven (uit)'
or esname='Intermac (Out)' or esname='Waterjet (Out)' or esname='Vacuum Lagen (Out)' or esname='RBB (Out)') and len(etikettnr)=9

group by etikettnr,esname;

LET vRowCounter = NoOfRows('Master');
TRACE $(vRowCounter) rows loaded in table "Master";

SQL SELECT

    sperrzeit,
    zeit,
    etikettnr,
    esnr,
    auftnr,
    pos,
    esname,
    bearbzeit
FROM ..................;

big_dreams
Contributor III

Re: Time in shifts

what error you are getting??

can you post screen shot it??

Regards,

leroboy1980
New Contributor

Re: Time in shifts

Load error.PNG

Community Browser