Master Time Calendar

[#1]

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

[Anonymous]

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

;

[#3]

Thanks for your answer.

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

[Anonymous]

You'll need to share some sample data.

[#5]

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...

[MarcoWedel · Accepted solution]

Hi,

maybe helpful:

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

regards

Marco

[Anonymous]

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)

;

[#8]

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);