Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor III

Time Calculation Help

Hi Folks,

In the script side , I'm using below logic to match time 21:59:59

num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without subtraction of 0.0000001 the time 21:59:59 was not matched

What shall I should minus from below to get 20:45:00????

num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Please help!

Regards,

AS

6 Replies
amit_saini
Honored Contributor III

Re: Time Calculation Help

Any suggestion???

MVP
MVP

Re: Time Calculation Help

What are your input and expected output?

MVP
MVP

Re: Time Calculation Help

You requirement is not clear.

>>What shall I should minus from below to get 20:45:00????

>> num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?

Well it depends on the value of Date,tmp. I am not sure what you are trying to do. To get the value of time 20:45, use

     =#Time('20:45', 'hh:mm')

     =MakeTime(20, 45)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
amit_saini
Honored Contributor III

Re: Time Calculation Help

Hello,

What I'm doing is based on Shift table as interval match is looking in CAQ table and taking distinct count of serial number:

My Night Shift numbers are 100% matching with data source with below logic

IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp

But number are not matching for Day shift.

Shift Table :

Script:

Shift:

LOAD Shift_Id,

     Shift_Name,

     num(Start_Hour) as Start,

     num(End_Hour - Time#('00:00:01'),'0,0000000') as End,

     Line_Id,

     Facility_Id

FROM

[$(vDataPath)Shift.qvd]

(qvd);

//exit script;

Shifts:

//Shift_Early_Late:

NoConcatenate

LOAD

    *

Resident Shift

where End > Start;

Shift_Night:

NoConcatenate

LOAD

    *

Resident Shift

where Start > End;

Shift_Night_1:

Concatenate (Shifts)

LOAD

    Shift_Id,

     Shift_Name,

     Start,

     1 as End,

     Line_Id,

     Facility_Id

Resident Shift_Night;

Shift_Night_2:

Concatenate (Shifts)

LOAD

    Shift_Id,

     Shift_Name,

     0 as Start,

     End,

     Line_Id,

     Facility_Id

Resident Shift_Night;

drop tables Shift, Shift_Night;

CAQ:

LOAD      

     date(floor(Date.tmp)) as Date,

     num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!

     year(Date.tmp) as Year,

     month(Date.tmp) as Month,

     week(Date.tmp) as Week,

     Day(Date.tmp) as Day,

     num(Date.tmp) as NumDate,

//dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,

//    dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,

//    dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,

     *

     ;

LOAD

    *,

    hour(Date.tmp2) as Time.tmp,

    IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp  // Logic for night shift

    ;

LOAD

    num(Date) as Date.tmp2,

    timestamp(Date) as Date_Org,

     Facility_Id,

     Product,

     Variant,

     Station,

     Serial,

     Characteristic,

     Values,

     Status,

     DTTSERF

FROM

[$(vDataPath)CAQ_table.qvd]

(qvd);

left join (CAQ)

IntervalMatch (Time,Facility_Id)

Load distinct

    Start,

    End,

    Facility_Id

Resident Shifts;

left join(CAQ)

LOAD distinct

    Facility_Id,

    Start,

    End,

    Line_Id,

    Shift_Name

Resident Shifts;

drop table Shifts;

Thanks,

Amit

avkeep01
Valued Contributor

Re: Time Calculation Help

Hi Amit,

Not quite sure if I'm getting the question right but it seems to me that you can use a FLOOR statement in your script

When you use time or datetime it only displays the number in another format it doesn't drop the decimal values after the seconds.

DATETIME(FLOOR(Date.tmp *24*60*60)/ (24*60*60))

Floor is a function which round the values to the first lower integers, so 5.7 = 5.

A date is an integer, while the hours, minutes and seconds are decimal values. Multiplying with 24 makes the hours integers, when adding a multiply of 60 the minutes are integers etc.

So floor(date.tmp*24*60*60) / (24*60*60) rounds your date to seconds

Re: Time Calculation Help

Hi,

maybe you could use something like

Time#(Time(Frac(Date.tmp),'hh:mm:ss'),'hh:mm:ss')


to avoid Rounding Errors



See also:

Correct Time Arithmetic | Qlikview Cookbook  by Rob Wunderlich

convert seconds to hh:mm:ss in script


Reference DateTime for calculating occupancy



hope this helps


regards


Marco




Community Browser