topic Re: Master Calendar with Time in QlikView

Master Calendar with Time

Thu, 30 Jul 2015 14:14:14 GMT

Hi! I am trying to crete a Master Calendar with Time, with a one minute step. However Master Calendar:

1. shows null values

2.doesn't sort ascending

3.doesn't match fields in constant way on the sheet.

Any suggestion is welcome

The code is:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='hh:mm';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[applesp]:

LOAD [AddedTimeStamp],

[Open]

FROM [lib://Documents/Script.xlsx]

(ooxml, embedded labels, table is applesp);

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, 'MM/DD/YYYY hh:mm') as AddedTimeStamp

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

LOAD Floor(Min(AddedTimeStamp)) as MinDate,

Ceil(Max(AddedTimeStamp)) as MaxDate

Resident applesp ORDER BY AddedTimeStamp ASC;

I attach the excel file too.

Re: Master Calendar with Time

MK_QSL — Thu, 30 Jul 2015 14:49:52 GMT

applesp:
LOAD AddedTimeStamp, 
     Open
FROM
Script.xlsx
(ooxml, embedded labels, table is applesp);


MinMaxDates:  
LOAD Floor(Min(TimeStamp#(AddedTimeStamp, 'DD/MM/YYYY hh:mm:ss'))) as MinDate,   
     Floor(Max(TimeStamp#(AddedTimeStamp, 'DD/MM/YYYY hh:mm:ss'))) as MaxDate  
RESIDENT applesp;  

LET vMinDate = FieldValue('MinDate', 1);  
LET vMaxDate = FieldValue('MaxDate', 1);   
  
DROP TABLE MinMaxDates;   


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 
  DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))) as AddedDate, 
    TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as AddedTimeStamp  
AUTOGENERATE 1439 
WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Re: Master Calendar with Time

Fri, 31 Jul 2015 06:07:37 GMT

thank for your answer but it throws me this error 😕

Re: Master Calendar with Time

MK_QSL — Fri, 31 Jul 2015 08:24:55 GMT

This error is indicating that your vMinDate and vMaxDate is not generated in script.. Please share you full script here..

Re: Master Calendar with Time

Fri, 31 Jul 2015 08:39:29 GMT

that was all my script...:/ Also I use qlik sense if it matters..

Can I ask you something else? I have null values on the sheet by my code above, how can I fix it?

Re: Master Calendar with Time

MK_QSL — Fri, 31 Jul 2015 08:43:23 GMT

You can use the same script in your QlikSense also.

Check what is the value of vMinDate and vMaxDate !

Re: Master Calendar with Time

Fri, 31 Jul 2015 08:49:04 GMT

okok!! thank you!