Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mario-sarkis
Contributor II

Time Comparaison

Dear,

1- I have a time fields for example

Punch IN: 07:35

I need to create a field when the field is less then 07:45 in to assign the 07:45 instead of 07:35

Out Put:

Punch IN: 07:45

Punch IN : 10:00

I need to create a field when the field is grater then 09:00 in to assign the 09:00 instead of 10:00

Out Put:

Punch IN: 07:45

2- I need subtract two time fields Example:

08:30 - 07:45 = 45 minutes

08:30 - 09:00 = -30 minutes

Hope you can help

Thanks

1 Reply
Highlighted
delroekid
Contributor

Re: Time Comparaison


Sample:

LOAD * INLINE [

    IN, OUT

07:35, 17:00

07:45, 17:00

07:55, 17:00

10:00, 17:50

10:50, 17:50

09:00, 17:50

];




ProcessSample:

Load *

,interval(Reference830-PunchIN745,'hh:mm') as Field745

,interval(Reference830-PunchIN900,'hh:mm')as Field900

;



Load *

, if(PunchDec  < Reference745, Reference745,

PunchDec) as PunchIN745

,if(PunchDec  > Reference900, Reference900,PunchDec) as PunchIN900


,if(PunchDec  < Reference745, Reference745,

if(PunchDec  > Reference900, Reference900,PunchDec)) as PunchIN

;



Load



*

,


Interval(IN,'hh:mm') AS PunchDec


,Interval(Interval#('08:30','hh:mm'),'hh:mm') AS Reference830

,Interval(Interval#('07:45','hh:mm'),'hh:mm') AS Reference745

,Interval(Interval#('09:00','hh:mm'),'hh:mm') AS Reference900


Resident Sample;



Drop Table Sample;