Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Classify start and end dates in time Categories (Start und Enddatum in Zeitkategorien einordnen)

Hello There,

I have the following task. In our BDE - data I have a start time and an end time of the production of each layer .. In order to be considered legal surcharges in order to night shift / late shift, I have the number of hours in

the time ranges

0-4 clock,

4-20Uhr and

20 - 23.59 Clock

have as a key figure.

Example:

Start time of production

01/02/2015 23:00:00

End Time

02/02/2015 02:00:00

As a result I would have liked to 1 hour for the contract 25% (from 23-0 clock) and

2 hours for the contract 50% (night shift from 0-02 Clock)

I have already tried with several if then else statements, but do not get to the destination, so I would have to consider a lot of start and end times (as converted time course. Does anyone have an idea?

Here's my current level, which is not practical and not thought through.

if ((timestamp (Time Stamp (start time, 'YYYY - MM-DD HH: MM: SS'), h: mm ')>' 4:00 ') and

    (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') <=' 20:00 '), (ProdEnde - start time) * 24.0) as Zulage0,

    if ((timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') <=' 23:59 ') or (timestamp (timestamp (ProdEnde,' YYYY-- MM-DD HH: MM: SS '), h: mm') = '0: 00'),

    (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') - '20: 00') * 24.0) as Zulage25,

  

    if (

    (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') <=' 4:00 ') and (timestamp (timestamp (ProdEnde,' YYYY - MM DD HH: MM: SS '), h: mm')> '0:00') and

    (Timestamp (Time Stamp (start time, 'YYYY - MM-DD HH: MM: SS'), h: mm ')> =' 0:00 '),

    (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') - (timestamp (Time Stamp (start time,' YYYY - MM-DD HH: MM: SS ') , 'h: mm'))) * 24,

     if (

     (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') <=' 4:00 ') and (timestamp (timestamp (ProdEnde,' YYYY - MM DD HH: MM: SS '), h: mm')> '0:00') and

    (Timestamp (Time Stamp (start time, 'YYYY - MM-DD HH: MM: SS'), h: mm ') <=' 23:59 '),

    (Timestamp (timestamp (ProdEnde, 'YYYY - MM-DD HH: MM: SS'), h: mm ') - timestamp (timestamp (' 0:00 ',' HH: MM: SS '), h : mm ')) * 24.0))

    AS Zulage50,

Many Thanks!

Translated with Google Translate - Qlik Community Administrative Team

Hallo,

ich habe folgende Aufgabenstellung. In unseren BDE - Daten habe ich eine Startzeit der Produktion und eine Endzeit der jeweiligen Schicht.. Um gesetzliche Zuschläge zwecks Nachtschicht / Spätschicht zu berücksichtigen, muss ich die Stundenanzahl in

den Zeitbereichen

0-4 Uhr,

4-20Uhr und

20 - 23.59 Uhr

als Kennzahl haben.

Beispiel:

Startzeit der Produktion

01.02.2015 23:00:00

Endzeit

02.02.2015 02:00:00

Als Ergebnis hätte ich gerne 1 Stunde für den Zuschlag 25% (nämlich von 23-0 Uhr) und

2 Stunden für den Zuschlag 50% (Nachtschicht von 0-02 Uhr)

Ich habe es schon mit diversen if then else Statements versucht, komme aber nicht zum Ziel, da ich so jede Menge Start und Endzeiten berücksichtigen müsste (als Uhrzeit konvertiert natürlich. Hat jemand ein Idee?

Hier mal mein jetziger Stand, der nicht besonders praktikabel und auch noch nicht zu Ende gedacht ist.

if ( (timestamp(Timestamp(StartZeit, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm')>'4:00') and

    (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') <='20:00'), (ProdEnde - StartZeit)*24,0) as  Zulage0,

    if ( (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') <='23:59') or (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') ='0:00'),

    (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') - '20:00')*24,0) as  Zulage25,

  

    if (

    (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') <='4:00') and (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') >'0:00') and

    (timestamp(Timestamp(StartZeit, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm')>='0:00'),

    (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') - (timestamp(Timestamp(StartZeit, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm')))*24,

     if (

     (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') <='4:00') and (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm') >'0:00') and

    (timestamp(Timestamp(StartZeit, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm')<='23:59'),

    (timestamp(Timestamp(ProdEnde, 'YYYY--MM-DD HH:MMSmiley FrustratedS'),'h:mm')- timestamp(Timestamp('0:00', 'HH:MMSmiley FrustratedS'),'h:mm'))*24,0))

    as  Zulage50,

Vielen Dank!

Community Browser