Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to find an error in some script that was developed for us by a third party. We have a report that has stopped working due to the knock on effect of our date calendar now projecting to 2020. I can see in the variables that a variable called varMaxDate is calculating a value 43887 (in Excel this is showing me it is 26/02/2020 which is the date in Qlik too).
I have used the find function to look through all of the sections within the script and the only mention of the variable varMaxDate is in the below script.
//CalDate field exists elsewhere in script and is the link. Example: date(InvoiceDate, 'DD/MM/YYYY') as [CalDate],
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(CalDate) as minDate,
max(CalDate) as maxDate
Resident Fact;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
MonthStart(date([TempDate],'YYYYMM')) as Period1,
Date(monthstart(TempDate), 'YYYYMM') as CalYearMonth,
12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,
// MonthStart(date([TempDate],'YYYYMM')) as Period1,
TempDate AS CalDate,
Date(monthstart(TempDate), 'MMM-YY') as CalMonthYear,
week(TempDate) As CalWeek,
Year(TempDate) As CalYear,
Month(TempDate) As CalMonth,
Day(TempDate) As CalDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as CalQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CalWeekYear,
weekstart(date(TempDate, 'DD/MM/YYYY')) as [CalWeekCommencing],
WeekDay(TempDate) as CalWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
As I'm still new to Qlik I cannot figure out why this script is generating 26/02/2020 for the calendar. Any help would be great!
That is helpful Mauritz, thank you.
remove Num() before variable creation
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
then try ones