Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qw_johan
Creator
Creator

Calendar with time (timestamp)

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

17 Replies
Not applicable

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
Creator
Creator
Author

qw_johan
Creator
Creator
Author

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

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
Creator
Creator
Author

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.

Not applicable

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

Not applicable

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?

Unbenannt.JPG.jpg

THanks for any help!

Kind regards,

Anthony