Calendar with time (timestamp)

[qw_johan]

Hi!

I have a MasterCalendar that works great. I am now trying to customize it so that it holds time too, using timestamp.
I saw an example here on how to do it but I can't get my code to work. Can anybody see what I am doing wrong?
Any help is appreciated
I include my qvw file. I keep getting error message : Error in expression: ')' expected
For what I can tell I am not missing any ")"...
Below is the code that qlikview doesn't like...[:)]

TempCalendar:

LOAD

$(varMinTimestamp) + rowno() - 1 AS Num,

Timestamp(Timestamp#($(varMinTimestamp), 'YYYYMMDD hh:mm:ss') + rowno() - 1) AS tempTimestamp

AUTOGENERATE

$(varMaxTimestamp) - $(varMinTimestamp) + 1;

Thankful for any help

[#2]

Hi Miguel

I adapted it to

timestamp($(vMinDatum) + (RecNo()/12/24) + (IterNo()-1)) as Minutegeneriert

And this gives me the preferred 5 Min intervals... However i am not sure if they are all created?!

Is there a max number of autgenerated events???

BRGDS,

Anthony

[#3]

Hi Miguel,

I would like to know is there any difference in Qlik 9 vs 11. Since i used your code (above) and it was working in Qlik 9. Now i am using 11 and it is throwing errors at this

1. CalendarTemp: 
2. LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date  
3.      TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp 
4. AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

[marinasbc]

Hi Miguel,

I'm using this piece of code combined with the remaining master calendar script for time with hour minute but I'm having an issue: it seems that I can't get the code to generate the hh:00 time.

Load

floor(MIN(timestamp#(CAL_DATETIME, 'DD-MM-YYYY hh:mm')))

as MinDate,

(...)

LET vMinDate = peek('MinDate',0);

(...)

LOAD

TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) AS AddedTimeStamp

If I try this for the 1st recno in the 1st iterno, then I'm getting, for vMinDate 41914:

02/10/2014 00:01

It seems I can't get the first minute of each hour (hh:00) but only the 2nd (hh:01). I can't figure out what I'm doing wrong here, could you please help?

Thanks you very much,

Marina

[#5]

Hi,

I am also getting same error when used in Qlikview 11.2, Can any one please suggest me how to fix this.

Regards,

Savi

[OmarBenSalem]

Hi Miguel,

I was trying to do the same thing so I've happily used your script.

here how I did:

MinMaxDates: 

LOAD Floor(Min(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MinDate,  

     Floor(Max(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MaxDate 

RESIDENT MyTable; 

LET vMinDate = FieldValue('MinDate', 1); 

LET vMaxDate = FieldValue('MaxDate', 1);  

DROP TABLE MinMaxDates;  

CalendarTemp: 

LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date  

     TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp 

AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

Calendar:LOAD AddedTimeStamp AS MyTimeStamp,  

     Year(AddedTimeStamp) AS MyYear

MasterCalendar:

LOAD    AddedTimeStamp  AS MyTimestamp,

Year(AddedTimeStamp)  AS MyYear,

Month(AddedTimeStamp)  AS MyMonth,

Day(AddedTimeStamp)  AS MyDay,

Hour(AddedTimeStamp)  AS MyHour,

Minute(AddedTimeStamp)  AS MyMinute,

Second(AddedTimeStamp)  AS MySecond

RESIDENT CalendarTemp

ORDER BY AddedTimeStamp  ASC;

DROP TABLE CalendarTemp;

All went good and I can see my new fields (MyHour etc) generated in the correct time frame.

Now, the problem is that when I try to filter with MyHour, MyMinute and so one, the graphs I've created doesn't change.

In fact, filtering with those fields does not affect the the Date_Timestamp field box (does not turn green nore even white) when I select an hour in the MyHour field.

Why that?

What should I do?

I'm reaaly confused !

[bbmmouha]

hello

it greate work

but you didn't do the join between master tempstime and your dimention table

[bbmmouha]

hello

did you fixe it i have the same problem and i can't solve it

thanks

[Anonymous]

Hi omar, you could build the query to make the master calendar with hours??

I have the same problem.