Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Master Calendar

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 

9 Replies
Anonymous
Not applicable
Author

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

its_anandrjs

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

Anonymous
Not applicable
Author

Hi Anand

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

Regards.

Chris

Anonymous
Not applicable
Author

HI Bill

Many thanks for the link.

Regards.

Chris

Anonymous
Not applicable
Author

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:

Screen Shot 2014-11-29 at 19.18.05.png

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

Anonymous
Not applicable
Author

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

its_anandrjs

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

Anonymous
Not applicable
Author

Hi

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

Anonymous
Not applicable
Author

Hi Anand

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