topic Re: Serviceable Hrs Logic in QlikView

Serviceable Hrs Logic

Mon, 13 Feb 2012 11:49:19 GMT

Please find the attached excel sheet for understanding the requirement.

The data is the raw data on which we need to put the logic below:

Based on

Avail Starttime and Avail EndTime we have to calculate the new start time and end time as enterted in the excel.

this has to be calculated based the sheet2 (service hrs)

The rquirement is we have to caluculation the down time based on the servicable Hrs only

For example

IF TermID 1 is availble from 6:00 Am to 4:00 PM

and if a fault had happened

5:00 AM and Fixed @ 7:00 Am the we have to calculate on from 6:00 amd to 7:00 and i.e. 1 hrs instead of 2 hrs.

Becuase it is available from 6 'o clock only

Please help me on the same.

Thanks and Regards

Venkatesh Salla

Re: Serviceable Hrs Logic

CELAMBARASAN — Mon, 13 Feb 2012 13:24:25 GMT

Hi,

Try with this approach,

TempTable:
LOAD TermId,
     [Customer Access],
     Avail_StartTime,
     Avail_EndTime
FROM
DataFile;
Left Join(TempTable)
LOAD [Customer Access],
     AccessStartTime,
     AccessEndTime
FROM
DataFile;
MainTable:
LOAD
          TermId,
          [Customer Access],
          Avail_StartTime,
  Avail_EndTime,
  if(Num(Avail_StartTime) >= Num(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime)) + AccessStartTime), Avail_StartTime, Date(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime))+AccessStartTime,'DD/MM/YYYY hh:mm:ss TT')) AS NewStartTime,
          if(Num(Avail_EndTime) <= Num(MakeDate(Year(Avail_EndTime),Month(Avail_EndTime),Day(Avail_EndTime)) + Time(AccessEndTime)), Avail_EndTime, Date(MakeDate(Year(Avail_EndTime),Month(Avail_EndTime),Day(Avail_EndTime))+Time(AccessEndTime),'DD/MM/YYYY hh:mm:ss TT')) AS NewEndTime
Resident
TempTable;
DROP Table TempTable;

Hope it helps

Celambarasan

Serviceable Hrs Logic

Mon, 13 Feb 2012 14:53:58 GMT

Thank you very much for the prompt and correct reply

Regards

Venkatesh Salla

Serviceable Hrs Logic

Mon, 13 Feb 2012 17:35:41 GMT

i would lie to know on more thing

what do if the end time is next day and i want to calculate till the current day(service able) available time

Please help on this

Thanks & Regards

Venkatesh Salla

Serviceable Hrs Logic

CELAMBARASAN — Tue, 14 Feb 2012 06:37:08 GMT

Hi,

For that use expression below

if(Num(Avail_EndTime) <= Num(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime)) + Time(AccessEndTime)), Avail_EndTime, Date(MakeDate(Year(Avail_StartTime),Month(Avail_StartTime),Day(Avail_StartTime))+Time(AccessEndTime),'DD/MM/YYYY hh:mm:ss TT')) AS NewEndTime

Hope this is your need.

Celambarasan