topic Re: Business Hours Calculation in QlikView

Business Hours Calculation

rash_611 — Wed, 22 Jan 2014 07:19:46 GMT

Hi,

I have the logic of finding the business hours but need to calculate between many fields. so is their a way to write in a function and call each time for different fields... please help me.... how to write TMP codes in a function

code for business hours:

INPUT:

LOAD

      CallNo,
        timestamp(opendate) as calldate,
        timestamp(resolvedate) as ResolveDate

timestamp(closedate) as closedate

from calldetails.qvd (qvd);

TMP:

LOAD

      CallNo,
        frac(calldate) as check,
        daystart(calldate)+iterno()-1 as Date,
        if(iterno()=1, rangemin(rangemax(frac(calldate),maketime(9,30)),maketime(17,30)), maketime(9,30)) as Start,
        if(WeekDay(daystart(calldate)+iterno()-1)='Sat', 'yes','no') as Check_sat,
        if(WeekDay(daystart(calldate)+iterno()-1)='Sat',

if(daystart(calldate)+iterno()-1=daystart(ResolveDate),

rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(14,30))),Maketime(14,30)),
if(daystart(calldate)+iterno()-1=daystart(ResolveDate),

rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(17,30))),Maketime(17,30))) as End

Resident INPUT;

left join (INPUT)
LOAD
CallNo,
interval(sum(End-Start)) as Duration
Resident TMP where WeekDay(Date)<6 and not match(Date,$(vHol)) group by CallNo;

Re: Business Hours Calculation

rash_611 — Wed, 22 Jan 2014 10:56:46 GMT

can any one help me plz......

Re: Business Hours Calculation

jonathandienst — Wed, 22 Jan 2014 11:29:05 GMT

It sound like you need a preceding load. Something like this (not syntax checked):

TMP:

LOAD *,

If(Check_sat = 'yes',

If(Date = daystart(ResolveDate),

_satEnd, Maketime(14,30)),

If(Date = daystart(ResolveDate),

_othEnd, Maketime(17,30))

) As End

;

LOAD

CallNo,

frac(calldate) as check,

daystart(calldate)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(calldate),maketime(9,30)),maketime(17,30)), maketime(9,30)) as Start,

if(WeekDay(daystart(calldate)+iterno()-1)='Sat', 'yes','no') as Check_sat,

rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(14,30))) As _satEnd,

rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(17,30))) As _othEnd

Resident INPUT;

DROP Fields _satEnd, _othEnd;

The drop statement just clears up unneeded fields. You can omit that if you want to use these calcs again somewhere else.

HTH

Jonathan

Re: Business Hours Calculation

rash_611 — Wed, 22 Jan 2014 11:32:46 GMT

hi, thanks for the reply... but i need to calculate business hours for more than 5 fields so i want to reuse the code.. how do i do it...

Re: Business Hours Calculation

tresB — Wed, 22 Jan 2014 12:09:44 GMT

Have you thought of using subroutines? Like

Sub YourSub(parameters)

....

End sub

Then recall it using CALL

Re: Business Hours Calculation

rash_611 — Sat, 25 Jan 2014 09:10:54 GMT

yes, i have used the subroutines but not able to like that my model how to do that....