topic Re: Calendar in QlikView

Calendar

kev6brown — Wed, 25 Nov 2020 16:16:04 GMT

I have multiple fact tables (subset of script below) and need to create a calendar it. I'm trying to use -

Episode:
LOAD NHS,
Date,
Area
FROM
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Episode);

Food:
LOAD NHS,
FoodDate,
FoodArea
FROM
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Food);

Physio:
LOAD NHS,
PhysioDate,
PhysioArea
FROM
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Physio);

DateLink:
LOAD
NHS
,Date as Date1
,'Episode' as DateType
RESIDENT Episode
;
LOAD
NHS
,FoodDate as Date1
,'Food' as DateType
RESIDENT Food
;
LOAD
NHS
,PhysioDate as Date1
,'Physio' as DateType
RESIDENT Physio;

CALL CalendarFromField('Date1', 'CommonCalendar','');
CALL CalendarFromField('EpisodeDate', 'EpisodeCalendar', 'Episode ');
CALL CalendarFromField('FoodDate', 'FoodCalendar', 'Food ');
CALL CalendarFromField('PhysioDate', 'PhysioCalendar', 'Physio ');

But I'm getting a 'Semantic Error', can anyone help?

Thanks

Kev

Re: Calendar

Anil_Babu_Samineni — Tue, 18 Dec 2018 00:15:24 GMT

I didn't understand why CALL function added?

CALL function works whenever you have SUB in the script. Is there any such?

To create calendar change all datethe fields as one and make a flag for each. Then the date field to build master calendar. This is straight forward after that..

Re: Calendar

prma7799 — Tue, 18 Dec 2018 06:41:08 GMT

Try like this

TEMP:

LOAD Max(Date) AS MaxDate
FROM
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Episode);
Concatenate
LOAD Max(FoodDate) AS MaxDate
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Food);
Concatenate
LOAD Max(PhysioDate) AS MaxDate
FROM
[C:\Users\KBrown\Documents\QlikView Examples\Calendar\Book1.xlsx]
(ooxml, embedded labels, table is Physio);

/**********************Select Min and Max Date from 'TEMP' Table*************/
LET varMinDate = Num(MakeDate(2012,04,01));
LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS [Posting Date],
TempDate AS %Date,
YearName(TempDate,0,4) AS FinancialYear,
Date(Yearstart(TempDate,0,4)) AS YearStart,
floor(TempDate) as LinkCommonDate,
Date(Yearend(TempDate,0,4)) AS YearEnd,
date(addyears( Date(Yearstart(TempDate,0,4)), -1), 'DD/MM/YYYY') as LYearStart,
date(addyears( Date(Yearend(TempDate,0,4)), -1), 'DD/MM/YYYY') as LYearEnd,
Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
Month(TempDate) AS MonthName,
If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,
MonthStart(TempDate) AS MonthStart,
MonthEnd(TempDate) AS MonthEnd,
Year(TempDate) AS Year,
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
quarterstart(TempDate,0,4) AS QuarterStart,
quarterend(TempDate,0,4) AS QuarterEnd,
quartername(TempDate,0,4) AS QuarterName,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,
AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
NUM(TempDate) AS DateNum,
monthname(TempDate) AS CalMonthYear,
Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon

RESIDENT TempCalendar
ORDER BY TempDate Asc;

DROP Table TempCalendar;