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 dude,

go through this link, it may helps u..

http://community.qlik.com/forums/t/35921.aspx

regards

A'run'

[Miguel_Angel_Baeyens]

Hello Johan,

If what you are looking for is a calendar that returns all possible hour:minute combination for each day, you should try with something like

MyTable:
LOAD DayStart(TimeStamp(TimeStamp#('01/01/2011 00:00', 'DD/MM/YYYY hh:mm') + (RecNo()/60/24) + (IterNo() -1))) AS AddedDate, 
     TimeStamp(TimeStamp#('01/01/2011 00:00', 'DD/MM/YYYY hh:mm') + (RecNo()/60/24) + (IterNo() -1)) AS AddedTimeStamp
AUTOGENERATE 1439 WHILE Num(Date('01/01/2011') + IterNo() -1) <= Num(Date('31/01/2011'));

AddedDate is the date, while AddedTimeStamp is the complete timestamp with hours and minutes.

Is that what you are looking for?

[qw_johan]

Thank you guys for your replys.

Miguel, you're close. Maybe I just didn't understand how to implement your code in my qvw .... What I am after is, if you check out the qvw file, is to create a calendar that holds all possible hour and minute (also likely seconds) combination for each day between oldest timestamp in MyTimestamp and the newest timestamp in MyTimestamp.

This is how I find oldest and newest timestamp.
LOAD
min(MyTimestamp) as MinTimestamp,
max(MyTimestamp) as MaxTimestamp
Resident MyTable;

This is how I create my calendar. (This is also where I get an error, see earlier post)
LOAD

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

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

AUTOGENERATE

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

Thanks, Johan

[Miguel_Angel_Baeyens · Accepted solution]

Hello Johan,

TimeStamp() returns a decimal number (in QlikView, hours are the 1/24 part of a day, and minutes 1/24/60, and seconds and so on). So instead of a timestamp, use a date, which is what you set in your min and max values for dates, so the code looks like

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,// And so

This will create a Calendar with all possible timestamps between the first second (00:00:00) of the first date to the last second (23:59:59) of the last date in your table. It will take long to load depending on the number of dates you have in your model (86399 records per day).

Hope that helps

[qw_johan]

HI!

This was what I was after. Wow, thanks.
My date inteval isn't too long, usually just a week or two so loading time won't be a problem for me.

Thanks again for your help.

[#7]

Hi,

I used this code but when i tried to link it to my table (using timestamp field) i wasn't able to get the result I wanted..please help

[Miguel_Angel_Baeyens]

Mars,

You must respect the format of your date field. I'd suggest you to create a new discussion in the Development Desktop QlikCommunity uploading some sample data from your timestamp fields. For what it's worth, the Timestamp() function in QlikView has nothing to do with some RDBM such as SQL Server "timestamp" type fields.

Regards.

Miguel

[#9]

Hi Miguel

I adapted your generator for minutes into my script and it's working fine!

Thanks!

However what i want to do is to create all 5 Min intervals between MinDate and MaxDate.

e.g. if no event took place between 00:00 and 00:05 i still want to create a value 00:00 for Field 5 Min interval ind order to do rolling period (5 Min) analysis later.

How do i need to change the formula in order to create every 5 min timestamp in stead of every minute?

THanks for any help!

Kind regards,

Anthony