Master Calendar with Time

[#1]

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.

[MK_QSL]

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

[#3]

thank for your answer but it throws me this error 😕

[MK_QSL]

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

[#5]

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?

[MK_QSL]

You can use the same script in your QlikSense also.

Check what is the value of vMinDate and vMaxDate !

[#7]

okok!! thank you!