topic Calendar with time (timestamp) in QlikView

Calendar with time (timestamp)

qw_johan — Wed, 16 Mar 2011 23:20:26 GMT

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

Calendar with time (timestamp)

Thu, 17 Mar 2011 05:03:34 GMT

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)

Miguel_Angel_Baeyens — Thu, 17 Mar 2011 05:33:05 GMT

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?

Calendar with time (timestamp)

qw_johan — Thu, 17 Mar 2011 13:54:38 GMT

Calendar with time (timestamp)

qw_johan — Thu, 17 Mar 2011 13:55:18 GMT

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)

Miguel_Angel_Baeyens — Thu, 17 Mar 2011 14:32:50 GMT

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

Calendar with time (timestamp)

qw_johan — Thu, 17 Mar 2011 16:02:48 GMT

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.

Re: Calendar with time (timestamp)

Thu, 13 Sep 2012 05:54:27 GMT

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)

Miguel_Angel_Baeyens — Thu, 13 Sep 2012 07:38:14 GMT

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

Re: Calendar with time (timestamp)

Tue, 15 Jul 2014 06:43:03 GMT

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

Re: Calendar with time (timestamp)

Tue, 15 Jul 2014 09:42:12 GMT

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

Re: Calendar with time (timestamp)

Fri, 24 Oct 2014 00:17:48 GMT

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

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

Re: Calendar with time (timestamp)

marinasbc — Thu, 11 Dec 2014 16:15:45 GMT

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

Re: Calendar with time (timestamp)

Thu, 26 Mar 2015 10:36:26 GMT

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

Re: Calendar with time (timestamp)

OmarBenSalem — Tue, 16 Feb 2016 13:22:08 GMT

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 !

Re: Calendar with time (timestamp)

bbmmouha — Fri, 29 Jun 2018 08:04:58 GMT

hello

it greate work

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

Re: Calendar with time (timestamp)

bbmmouha — Fri, 29 Jun 2018 12:19:27 GMT

hello

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

thanks

Re: Calendar with time (timestamp)

Anonymous — Fri, 14 Sep 2018 21:36:01 GMT

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

I have the same problem.