Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Serviceable Hrs Logic

Hi

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

1 Solution

Accepted Solutions
Highlighted

Re: Serviceable Hrs Logic

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

View solution in original post

4 Replies
Highlighted

Re: Serviceable Hrs Logic

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

View solution in original post

Highlighted
Not applicable

Serviceable Hrs Logic

Hi

Thank you very much for the prompt and correct reply

Regards

Venkatesh Salla

Highlighted
Not applicable

Serviceable Hrs Logic

HI

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

Highlighted

Serviceable Hrs Logic

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