Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.


Community Browser