Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pskumars
New Contributor II

business hours and rest as non business hours.

HI All,

I Have date data below.

i need to calculate time between 8:00 to 20:00 as business hours and rest as non business hours.

 

DateTime
11/16/2016 15:00
11/16/2016 15:30
11/17/2016 14:30
11/21/2016 9:30
11/21/2016 10:00
11/21/2016 10:30
11/21/2016 11:00
11/21/2016 11:30
11/22/2016 7:30
11/22/2016 12:00
11/23/2016 11:30
11/24/2016 11:30
11/29/2016 9:30
12/1/2016 10:30
12/1/2016 11:00
1 Solution

Accepted Solutions
MVP
MVP

Re: business hours and rest as non business hours.

May be try this

If(Frac(DateTime) > MakeTime(7, 59, 59) and Frac(DateTime) < MakeTime(20, 0, 1), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]

8 Replies
MVP
MVP

Re: business hours and rest as non business hours.

May be like this

LOAD DateTime,

     If(DateTime >= MakeDate(8) and DateTime <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

     ....

FROM ...

pskumars
New Contributor II

Re: business hours and rest as non business hours.

Not working Sunny,thanks for suggestion

neelamsaroha157
Valued Contributor II

Re: business hours and rest as non business hours.

Does this work for you..

pskumars
New Contributor II

Re: business hours and rest as non business hours.

no

MVP
MVP

Re: business hours and rest as non business hours.

Forgot to add Frac function

LOAD DateTime,

    If(Frac(DateTime) >= MakeDate(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

    ....

FROM ...

MVP
MVP

Re: business hours and rest as non business hours.

Found another issue

If(Frac(DateTime) >= MakeTime(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]

pskumars
New Contributor II

Re: business hours and rest as non business hours.

Hi Sunny ,

it works but there is an issue , 20:00 also ates.JPGdding as non business hours.

MVP
MVP

Re: business hours and rest as non business hours.

May be try this

If(Frac(DateTime) > MakeTime(7, 59, 59) and Frac(DateTime) < MakeTime(20, 0, 1), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]