Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qw_johan
Contributor

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...[Smiley Happy]

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

Re: Calendar with time (timestamp)

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

17 Replies
Not applicable

Calendar with time (timestamp)

hi dude,

go through this link, it may helps u..

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

regards

A'run'

Re: Calendar with time (timestamp)

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
Contributor

Calendar with time (timestamp)

qw_johan
Contributor

Calendar with time (timestamp)

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



Re: Calendar with time (timestamp)

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
Contributor

Calendar with time (timestamp)

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

Re: Calendar with time (timestamp)

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

Re: Calendar with time (timestamp)

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

Re: Calendar with time (timestamp)

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

Community Browser