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.
[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);
// 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];
