topic Re: Master Time Calendar in QlikView

Master Time Calendar

Fri, 20 Nov 2020 18:02:30 GMT

Hi, I am trying to connect my data with a Master Time Calendar that I initialize in this way:

TempTime:
Load
Time(RecNo() /24/60) as TimeNum
AutoGenerate 24*60;

Time:
Load
TimeNum as MyTimeValue,
Hour(TimeNum) as Hour,
Minute(TimeNum) as Minute
Resident TempTime; Drop table TempTime;

where MyTimeValue is a field that I create in another table such as:

MyTable:

Load

MyTimeValue.hours,

MyTimeValue.minutes

Time(maketime(MyTimeValue.hours, MyTimeValue.minutes), 'hh:mm') as MyTimeValue

Now, it happens something strange, not all the time values are linked with the table Time, meaning that not all the value have an Hour. Why the application is linking only some values? How should I connect them all? There is something wrong in my way of implementing the Master Time Calendar?

Below you can find an image that better explain the problem:

Thank you in advance

Re: Master Time Calendar

Anonymous — Mon, 23 Nov 2015 18:43:03 GMT

Looks like your number representations for MyTimeValue are not matching exactly.

This works for me :

TempTime:

Load

Time(RecNo() /1440) as TimeNum

AutoGenerate 1440

;

Time:

Load

TimeNum as MyTimeValue,

Hour(TimeNum) as Hour,

Minute(TimeNum) as Minute

Resident TempTime; Drop table TempTime

;

MyTime:

Load

rowno() ,

Time(round(maketime(Hour, Minute),1/1440), 'hh:mm') as MyTimeValue

resident Time

;

Re: Master Time Calendar

Mon, 23 Nov 2015 19:00:41 GMT

Thanks for your answer.

Apparently by implementing your solution something changed, more data have been linked, but the problem still is there:

Re: Master Time Calendar

Anonymous — Mon, 23 Nov 2015 19:05:34 GMT

You'll need to share some sample data.

Re: Master Time Calendar

Mon, 23 Nov 2015 19:17:53 GMT

Attached you can find the dataset.

Because MyTimeValue is in the form HH.MM where the minutes are represented in 1/100, I convert this time stamp in my script:

Table_temp:
LOAD userid,

     MyTimeValue,
      floor(MyTimeValue) as MyTimeValue.hours,
      floor(frac(MyTimeValue)*0.6*100) as MyTimeValue.minutes
FROM...

Table:
LOAD userid,

Time(maketime(MyTimeValue.hours, MyTimeValue.minutes), 'hh:mm') as MyTimeValue

resident...

Re: Master Time Calendar

MarcoWedel — Tue, 24 Nov 2015 00:19:48 GMT

Hi,

maybe helpful:

http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html

regards

Marco

Re: Master Time Calendar

Anonymous — Tue, 24 Nov 2015 18:33:59 GMT

How does the attached qvw look like to you ?

Below I have pasted in the load script.

TempTime:

Load

Time(RecNo() /1440) as TimeNum

AutoGenerate 1440

;

Time:

Load

TimeNum as MyTimeValue,

Hour(TimeNum) as Hour,

Minute(TimeNum) as Minute

Resident TempTime

;

Drop table TempTime ;

Table:

LOAD

userid,

Time(maketime(MyTimeValue.hours, MyTimeValue.minutes), 'hh:mm') as MyTimeValue

;

LOAD

userid,

floor(MyTimeValue) as MyTimeValue.hours,

floor(frac(MyTimeValue)*0.6*100) as MyTimeValue.minutes

FROM

dataset.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

;

Re: Master Time Calendar

Tue, 24 Nov 2015 21:06:44 GMT

As it is in your solution, not all the values are linked. Below I attach my solution. It basically considers that QlikView represents Time in two ways: externally as string values and internally as numeric values from 0 to 1, as reported in the very useful link Marco attached (http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html). I then applied the method Time#(Time( )) to both the time values that are linked:

TempTime:
Load
   Time(RecNo() /1440) as TimeNum
   AutoGenerate 1440
;

Time:
Load
   Time#(Time(TimeNum,'hh:mm')) as MyTimeValue,
   Hour(TimeNum) as Hour,
   Minute(TimeNum) as Minute
   Resident TempTime
;
Drop table TempTime ;

Table:
LOAD
   userid,
   Time#(Time(maketime(MyTimeValue.hours, MyTimeValue.minutes), 'hh:mm')) as MyTimeValue
;
LOAD userid,

floor(MyTimeValue) as MyTimeValue.hours,

floor(frac(MyTimeValue)*0.6*100) as MyTimeValue.minutes

FROM dataset.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);