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

Mon, 29 Jul 2013 13:38:07 GMT

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.

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

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