Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

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
bill_markham
Champion III
Champion III

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

chitemerere
Specialist
Specialist
Author

Hi Anand

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

Regards.

Chris

chitemerere
Specialist
Specialist
Author

HI Bill

Many thanks for the link.

Regards.

Chris

chitemerere
Specialist
Specialist
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

bill_markham
Champion III
Champion III

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

chitemerere
Specialist
Specialist
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

chitemerere
Specialist
Specialist
Author

Hi Anand

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