Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to Create a link table and master calendar from multiple dates

Hi guys looking for some help,

I am trying to create a link table from 3 diferent tables that contain three different date fields. I then want to create one master calendar for the application.

Tables:

FactoryData - Date

SupplierData - SupplierCreatedDate

CorrectiveActions - ModifiedDate

I have tried the following but not having much luck as there is no association with anything.

//---------Calendar Link Table----------------------

Link:

LOAD

          %SupplierKey,

          Date AS LinkDate,

          'FactoryDate' AS DateType

RESIDENT FactoryData;

CONCATENATE (Link)

LOAD

          %SupplierKey,

          SupplierCreatedDate AS LinkDate,

          'SupplierDate' AS DateType

RESIDENT SupplierData;

CONCATENATE (Link)

LOAD

          %CorrectiveActionKey AS %CorrectiveActionKey_Link,

          [Modified Date] AS LinkDate,

          'CorrectiveActionDate' AS DateType

RESIDENT CorrectiveActionData;

//--------------------------Master Calendar--------------------------------------------

TempMinMax:

LOAD

          MIN(LinkDate) AS MinDate,

          MAX(LinkDate) AS MaxDate

RESIDENT Link;

Let vMinDate = num(Peek('MinDate', '0', MinMax));

//LET vMinDate = NUM(ADDMONTHS(YEARSTART(PEEK('MinDate',0)),-24));

LET vMaxDate = PEEK('MaxDate',0,'TempMinMax');

MasterCalendar:

LOAD

          LinkDate,

          WEEK(LinkDate) AS Week1,

          MONTH(LinkDate) AS Month1,

          'Q'&CEIL(MONTH(LinkDate) /3) AS Quarter1,

          IF(WEEK(LinkDate)=53 AND num(MONTH(LinkDate))=1, WEEK(LinkDate)&'-'&numsum(YEAR(LinkDate)-1), WEEK(LinkDate)&'-'&YEAR(LinkDate)) AS WeekYear1,

          DUAL(MONTH(LinkDate)&'-'&YEAR(LinkDate),YEAR(LinkDate)&MONTH(LinkDate)) AS MonthYear1,

          'Q'&CEIL(MONTH(LinkDate) /3)&' '&YEAR(LinkDate) AS QuarterYear1,

          YEAR(LinkDate) AS Year1

;

LOAD

          $(vMinDate) + recno() -1 AS Num,

          DATE($(vMinDate) + recno() - 1) AS LinkDate

AUTOGENERATE

          ($(vMaxDate) - $(vMinDate) + 1);

DROP TABLE TempMinMax;

Also attached is a view of the data model.

Any help or solution would be appreciated.

Thanks in advance.