topic Re: Master Calendar in QlikView

Master Calendar

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

How does one incorporate two separate dates from two separate tables in the master calendar. In one table, i have DateDispensed (date medicine was dispensed in the pharmacy) which currently forms the basis of my master calendar. Now i want to add to my application a table with details on the date the medicine was approved (ActionDate). How can this be achieved? My master calendar looks as follows:

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([DateDispensed]))) as minDate,
max(Date(Floor([DateDispensed]))) as maxDate
Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID]
;
LOAD DISTINCT
TempDate as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

Filtered:
LOAD
Year as Last5Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 4 and Year <= Year(Today());

calTemp1:
LOAD Distinct
Year,
Week
Resident MasterCalendar
Order by Year, Week;

Left Join (MasterCalendar)
LOAD
Year,
Week,
RowNo() as weekYearSeq
Resident calTemp1
Order By Year, Week;
DROP Table calTemp1;

////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();

The other table where the date dispensed is as follows:

[Main Data]:
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,
     [Repeat or Original],
    [Doctor Name],
      [Doctor Number],
      QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      [Drug Code]

FROM
[$(vFolderSourceData)Datawarehouse_2.xls]
(biff, embedded labels, table is Datawarehouse$);

STORE [Main Data] into '$(vFolderSourceData)QVDs\
PrescriptionHistory.qvd' (qvd);

The new table i want to add is as follows:

[MCAZ APPROVED]:
LOAD [APPLICATION #]as ApplicationNumber,
      [TRADE NAME],
      [GENERIC NAME],
      FORM,
      STRENGTH,
      CATEGORY,
      [REGISTRATION #] as RegistrationNumber,
      ActionDate,
      APPLICANT,
      MANUFACTURER
//     MANUFACTURER1,
//     MANUFACTURER2,
//     manufname4,
//     manufcode5,
//     manufname5
FROM
[$(vFolderSourceData)tblMCAZApproved.xls]
(biff, embedded labels, table is regis_h$);

The field ActionDate is the new date to be incorporated into the master calendar.

Thanking you in advance.

Chris

Re: Master Calendar

Anonymous — Sat, 29 Nov 2014 12:44:27 GMT

This blog post by HIC is good and has links to other related posts

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: Master Calendar

its_anandrjs — Sat, 29 Nov 2014 13:30:29 GMT

Hi,

Try this expression for you i add a script for the common dates and the resident table to get the table flag fields

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

//*********************************New Table Add
Temp1:
Load Distinct
[DateDispensed] as CommonDate,
[DateDispensed],
'Main Data Table' as TableFlag
Resident [Main Data];

Concatenate(Temp1)
LOAD Distinct
ActionDate as CommonDate,
ActionDate,
'MCAZ APPROVED Table' as TableFlag
Resident [Main Data];

Temp2:
LOAD
min(Date(Floor(CommonDate))) as minDate,
max(Date(Floor(CommonDate))) as maxDate
Resident Temp1;

LET varMinDate = Num(Peek('minDate', 0, 'Temp2'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp2'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate,
Date($(varMinDate) + IterNo() - 1) as CommonDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

//*****************************
//For get the TableFlagfield into the calendar
Left Join(TempCalendar)
LOAD 
CommonDate,
TableFlag
Resident Temp1;

//***************************************
MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID];

LOAD DISTINCT
TempDate as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

Filtered:
LOAD
Year as Last5Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 4 and Year <= Year(Today());

calTemp1:
LOAD Distinct
Year,
Week
Resident MasterCalendar
Order by Year, Week;

Left Join (MasterCalendar)
LOAD
Year,
Week,
RowNo() as weekYearSeq
Resident calTemp1
Order By Year, Week;
DROP Table calTemp1;

////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();

//The other table where the date dispensed is as follows:

[Main Data]:
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,
     [Repeat or Original],
     [Doctor Name],
     [Doctor Number],
     QTY,
     [MedAid Amount],
     Shortfall,
     [Med Aid] as [Payment Type],
     [Supply days],
     [Drug Code]    
FROM
[$(vFolderSourceData)Datawarehouse_2.xls]
(biff, embedded labels, table is Datawarehouse$);

STORE [Main Data] into '$(vFolderSourceData)QVDs\
PrescriptionHistory.qvd' (qvd);



The new table i want to add is as follows:



[MCAZ APPROVED]:
LOAD [APPLICATION  #]as ApplicationNumber,
      [TRADE NAME],
      [GENERIC NAME],
      FORM,
      STRENGTH,
      CATEGORY,
      [REGISTRATION #] as RegistrationNumber,
      ActionDate,
      APPLICANT,
      MANUFACTURER
//     MANUFACTURER1,
//     MANUFACTURER2,
//     manufname4,
//     manufcode5,
//     manufname5
FROM
[$(vFolderSourceData)tblMCAZApproved.xls]
(biff, embedded labels, table is regis_h$);

Regards

Anand

Re: Master Calendar

Anonymous — Sat, 29 Nov 2014 17:06:08 GMT

Hi Anand

Many thanks, i will try it out and come back to you.

Regards.

Chris

Re: Master Calendar

Anonymous — Sat, 29 Nov 2014 17:27:26 GMT

HI Bill

Many thanks for the link.

Regards.

Chris

Re: Master Calendar

Anonymous — Sat, 29 Nov 2014 17:53:19 GMT

Hi Anand

i tried it out after changing line 30 from "Drop Table Temp;" to "Drop Table Temp2;" and I get the following error message:

I then changed line 20 from"Resident [Main Data]" to "Resident [MCAZ APPROVED]". The script loads well but now my application now shows "No Data to Display" on all sheets.

Regards.

Chris

Re: Master Calendar

Anonymous — Sat, 29 Nov 2014 18:30:14 GMT

Are you sure about dropping table Temp2 having only just created it.? Did you mean to drop table Temp1 instead ?

Re: Master Calendar

its_anandrjs — Sat, 29 Nov 2014 18:43:02 GMT

Hi,

Yes you right it is

Concatenate(Temp1)

LOAD Distinct

ActionDate as CommonDate,

ActionDate,

'MCAZ APPROVED Table' as TableFlag

Resident [MCAZ APPROVED];

Instead of Resident [Main Data];

Also check which tables you deleted and load one by one it works well.

Regards

Anand

Re: Master Calendar

Anonymous — Sun, 30 Nov 2014 06:48:23 GMT

The reason i did that is i do not see any reference to the table "temp" in the script. Which one should be dropped?

Regards

Chris

Re: Master Calendar

Anonymous — Mon, 01 Dec 2014 13:13:08 GMT

Hi Anand

Many thanks, the only area I changed in my application is the calendar, nothing else was changed.