topic Master Calendar with Hour Minute issue in QlikView

Master Calendar with Hour Minute issue

marinasbc — Fri, 12 Dec 2014 16:43:12 GMT

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

Re: Master Calendar with Hour Minute issue

MarcoWedel — Fri, 12 Dec 2014 22:04:55 GMT

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

Re: Master Calendar with Hour Minute issue

MarcoWedel — Fri, 12 Dec 2014 22:36:43 GMT

maybe like this:

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

Re: Master Calendar with Hour Minute issue

marinasbc — Thu, 18 Dec 2014 17:29:44 GMT

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