Using multiple Resident Load Statement

[pgloc2020]

Hi All,

I have a question in conjunction with my previous post.

https://community.qlik.com/t5/New-to-Qlik-Sense/Expressions-in-Resident-Load-Statement/m-p/2419612#M...

I have the same data but few more tables added in it now. 

[Table-1]:

LOAD
[CLOSED_DATE],

some more data
FROM [/Table-1.qvd]
(qvd);

[Table-2]:
LOAD

FROM [/Table-2.qvd]
(qvd);

[Table-3]:
FROM [/Table-3.qvd]
(qvd);

[Table-4]:

left join ([Table-1])

LOAD

 [DEPOSIT_ID],
[Service_Request_Id] // this is a joining key of Table 1

FROM [lib://.qvd]
(qvd);

[Table 4.1]:

LOAD

[DEPOSIT_ID-1]

FROM [lib://Table 4.qvd]
(qvd);

[Table -5]: // Note- this table is not directly join with any other table except Table 4

left join ([Table -4.1])

LOAD
 [DEPOSIT_ID-1], // this is a joining key of table 4
Date(Floor([Deposit_Receipt_Date])) as [Deposit_Receipt_Date]

FROM [lib://.qvd]
(qvd);

[FinalTable]:

LOAD
*,
If([DEPOSIT_ID]>=1,'Yes','No') AS [DepositReceived], // Note: To get Yes and No value, I have to have table 4.1 and join it with 5
[SR_SubType]&(If([Initial_Queue]='MPREGQ','MPREGQ', If([Initial_Queue]='MPREGQE', 'MPREGQE', 'any')))&(If([Public_Sector_Owner]='MPREGQ','MPREGQ', If([Public_Sector_Owner]='MPREGQE', 'MPREGQE', 'any')))&[X_FORM_TYPE]&[X_LC_GROUP_APPL_FORM]&[REG_TYPE] AS [MatchKey2]

Resident [Table-1];
//DROP TABLE [s_srv_req];

join ([FinalTable])
Load
*,
(Date(If([CLOSED_DATE]>1,[CLOSED_DATE],Today())))-[Deposit_Receipt_Date] AS [Deposit Claim Age]

Resident [Table-4];

My goal is to get [Deposit Claim Age] and [MatchKey2] columns.

I was able to get [MatchKey2] column as suggested by @Oleg_Troyansky in my previous post, but now I am trying to get [Deposit Claim Age] column as well. 

Please help me on how I can write the resident load statement. 

[F_B]

Hi @pgloc2020 ,

try this:

[Table-1]:
LOAD
[CLOSED_DATE],
some more data
FROM [lib://Table-1.qvd] (qvd);

[Table-2]:
LOAD
// specify columns
FROM [lib://Table-2.qvd] (qvd);

[Table-3]:
LOAD
// specify columns
FROM [lib://Table-3.qvd] (qvd);

[Table-4]:
LEFT JOIN ([Table-1])
LOAD
[DEPOSIT_ID],
[Service_Request_Id] // this is a joining key of Table 1
FROM [lib://Table-4.qvd] (qvd);

[Table-4.1]:
LOAD
[DEPOSIT_ID-1]
FROM [lib://Table-4.qvd] (qvd);

[Table-5]:
LEFT JOIN ([Table-4.1])
LOAD
[DEPOSIT_ID-1], // this is a joining key of Table 4
Date(Floor([Deposit_Receipt_Date])) AS [Deposit_Receipt_Date]
FROM [lib://Table-5.qvd] (qvd);

[FinalTable]:
LOAD
*,
If([DEPOSIT_ID] >= 1, 'Yes', 'No') AS [DepositReceived],
[SR_SubType] &
(If([Initial_Queue] = 'MPREGQ', 'MPREGQ', If([Initial_Queue] = 'MPREGQE', 'MPREGQE', 'any'))) &
(If([Public_Sector_Owner] = 'MPREGQ', 'MPREGQ', If([Public_Sector_Owner] = 'MPREGQE', 'MPREGQE', 'any'))) &
[X_FORM_TYPE] & [X_LC_GROUP_APPL_FORM] & [REG_TYPE] AS [MatchKey2]
RESIDENT [Table-1];

JOIN ([FinalTable])
LOAD
*,
(Date(If([CLOSED_DATE] > 1, [CLOSED_DATE], Today())) - [Deposit_Receipt_Date]) AS [Deposit Claim Age]
RESIDENT [Table-4];

// Ensure no duplicate columns after join
DROP FIELDS
[DEPOSIT_ID-1],
[Service_Request_Id]; // drop if no longer needed

// Optional: Drop intermediary tables if no longer needed to save memory
DROP TABLE [Table-1];
DROP TABLE [Table-2];
DROP TABLE [Table-3];
DROP TABLE [Table-4];
DROP TABLE [Table-4.1];
DROP TABLE [Table-5];