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: 
marinasbc
Partner - Contributor III
Partner - Contributor III

Master Calendar with Hour Minute issue

Hi all,

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 MinDate 41914 -> )

TimeStamp(41914 + (1/60/24) + (1 -1)) = 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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

as RecNo() also starts at 1, the last line actually should read:

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


but if you post some more of this script, then maybe we can abbreviate it a bit.



hope this helps


regards


Marco

View solution in original post

3 Replies
MarcoWedel

Hi,

as RecNo() also starts at 1, the last line actually should read:

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


but if you post some more of this script, then maybe we can abbreviate it a bit.



hope this helps


regards


Marco

MarcoWedel

maybe like this:

QlikCommunity_Thread_145648_Pic1.JPG

QlikCommunity_Thread_145648_Pic2.JPG

table1:

LOAD Timestamp#(CAL_DATETIME, 'DD-MM-YYYY hh:mm') as CAL_DATETIME

Inline [

CAL_DATETIME

10-02-2014 12:34

12-02-2014 18:24

14-02-2014 03:04

];

tabCalendar:

LOAD *,

    DayName(AddedTimeStamp) as Date,

    Hour(AddedTimeStamp) as Hour,

    Day(AddedTimeStamp) as Day,

    WeekDay(AddedTimeStamp) as WeekDay,

    Week(AddedTimeStamp) as Week,

    WeekName(AddedTimeStamp) as WeekName,

    Month(AddedTimeStamp) as Month,

    MonthName(AddedTimeStamp) as MonthName,

    Ceil(Month(AddedTimeStamp)/3) as Quarter,

    QuarterName(AddedTimeStamp) as QuarterName,

    Year(AddedTimeStamp) as Year,

    WeekYear(AddedTimeStamp) as WeekYear;

LOAD Timestamp(MinDate+(IterNo()-1)/1440, 'DD-MM-YYYY hh:mm') as AddedTimeStamp

While MinDate+(IterNo()-1)/1440<=MaxDate;

LOAD Floor(Min(CAL_DATETIME)) as MinDate,

    Ceil(Max(CAL_DATETIME)) as MaxDate

Resident table1;

hope this helps

regards

Marco

marinasbc
Partner - Contributor III
Partner - Contributor III
Author

YES, Thank you very much! That was exactly the problem, thanks!

Problem solved

Also, thank you for the reply with the master calendar!!

Cheers,

Marina