Linking two resident tables

Tue, 29 May 2012 09:54:56 GMT

I have a master calender table that I build up like this so I can link each type of date to a PoliyID, RiskID and ClaimID:

MasterDate:

LOAD TOP_Policy_StartDate as MasterDate,
Year(TOP_Policy_StartDate) as Year,
Month(TOP_Policy_StartDate) as Month,
Day(TOP_Policy_StartDate) as Day,
text(TOP_Policy_StartDate_FinYear) as FinYear,
text(TOP_Policy_StartDate_FinMonth) as FinMonth,
'PolicyStartDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident PolicyClient;

Concatenate (MasterDate)

LOAD TOP_Policy_CancelledDate as MasterDate,
Year(TOP_Policy_CancelledDate) as Year,
Month(TOP_Policy_CancelledDate) as Month,
Day(TOP_Policy_CancelledDate) as Day,
text(TOP_Policy_CancelledDate_FinYear) as FinYear,
text(TOP_Policy_CancelledDate_FinMonth) as FinMonth,
'PolicyCancelledDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident PolicyClient;

With Concatenation I know you have to have the field names in the exact same order and field names.

I have another table which is alink tables which links PolicyID, RiskID and ClaimID between the various tables

So I would have a table lets callit LinkTable with
Lnk_TOP_PolicyID
Lnk_TOP_RiskID
Lnk_TOP_ClaimID

Is what I am doign below correctand can I do multiple concatenations this way?

Concatenate (Masterdate)

LOAD TOP_ClaimDaily_Fulldate as MasterDate,
Year(TOP_ClaimDaily_Fulldate) as Year,
Month(TOP_ClaimDaily_Fulldate) as Month,
Day(TOP_ClaimDaily_Fulldate) as Day,
text(TOP_ClaimDaily_FinYear) as FinYear,
text(TOP_ClaimDaily_FinMonth) as FinMonth,
'ClaimTransDate' as DateType,
'' as Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
RESIDENT GenasysClaimsDaily;

JOIN (Masterdate)
LOAD
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
RESIDENT LinkTable;

topic Linking two resident tables in QlikView

Linking two resident tables