How to join another table with a resident table and then create an another resident table

[pgloc2020]

Hello all,

I have several tables joined with main table name [ABC]. Then I created a resident table and joined with Asset table. And because I need a new column, I am trying to create an another resident table. Not sure if this is the correct approach. The part I highlighted in bold is giving me an error. 

Please advise. 

Join[ResidentTable1]:

LOAD
*,
If([DEPOSIT_ID]>=1,'Yes','No') AS [DepositReceived]

Resident [ABC];

Concatenate

[test]:

load * Resident [ABC];

left join
load

[ROW_ID] AS [DEPOSIT_ID],
Date(Floor([REGISTERED_DT])) as [Deposit_Receipt_Date]
FROM [lib://ASSET.qvd]
(qvd);

[FinalTable]:

LOAD
*,
(Date(If([ACT_CLOSE_DT]>1,[ACT_CLOSE_DT],Today())))-[Deposit_Receipt_Date] AS [Deposit Claim Age]

Resident [ABC];
DROP TABLE [ABC];

[Lech_Miszkiewicz · Accepted solution]

• The main question is which table do you need "Deposit Claim Age" field to be in at the end?
• When you say that you have tables joined to ABC it means that you actually have table ABC in your data model, right? or did you mean that tables are associated with ABC table by the power of association of Qlik engine?

Looking at the whole script I question quite few things here - I will highlight them below:

Join[ResidentTable1]: // i would call it just ResidentTable1 - try to avoid using keywords like "join" which can mislead you or can actually get executed when used inappropriately

LOAD
*,
If([DEPOSIT_ID]>=1,'Yes','No') AS [DepositReceived]

Resident [ABC];

Concatenate // 1. Concatenate (Tablename!!!) Explicit concatenation would help, 2. What is the purpose of this concatenation? you created Join[ResidentTable1] from ABC and then you UNION to it all records from ABC again by using "Concatenate". That does not make much sense. 

[test]: // since you are using above "concatenate" statement that is obsolete

load * Resident [ABC];

left join // left join to what? again much better is explicit Left Join (Join[ResidentTable1])
load

[ROW_ID] AS [DEPOSIT_ID],
Date(Floor([REGISTERED_DT])) as [Deposit_Receipt_Date]
FROM [lib://RPP_Data_Space:DataFiles/S_ASSET.qvd]
(qvd);

[FinalTable]:

LOAD
*,
(Date(If([ACT_CLOSE_DT]>1,[ACT_CLOSE_DT],Today())))-[Deposit_Receipt_Date] AS [Deposit Claim Age]

Resident [ABC]; // Why ABC? ABC table does not have [Deposit_Receipt_Date] as you joined that to Join[ResidentTable1] so instead of "ABC" you should have "Join[ResidentTable1]" here.
DROP TABLE [ABC];

In my opinion this is simpler than it looks and it could be all handled in few lines of code like this: 

temp_Table:
LOAD
	*,
	If([DEPOSIT_ID]>=1,'Yes','No') 	as [DepositReceived]
Resident 
	[ABC]
;

Left Join (temp_Table)
LOAD
	[ROW_ID] 						as [DEPOSIT_ID],
	Date(Floor([REGISTERED_DT])) 	as [Deposit_Receipt_Date]
FROM 
	[lib://RPP_Data_Space:DataFiles/S_ASSET.qvd](qvd)
;

[FinalTable]:
LOAD
	*,
	If([ACT_CLOSE_DT]>1,[ACT_CLOSE_DT],Today())-[Deposit_Receipt_Date] AS [Deposit Claim Age]
Resident 
	temp_Table
;

DROP TABLE temp_Table;
DROP TABLE ABC;

or like this ( I am just not sure how rest of your models looks like so it is hard to judge but looking at what you were trying to do this could do the job and is so much simpler):

Left Join (ABC)
LOAD
	[ROW_ID] 						as [DEPOSIT_ID],
	Date(Floor([REGISTERED_DT])) 	as [Deposit_Receipt_Date]
FROM 
	[lib://RPP_Data_Space:DataFiles/S_ASSET.qvd](qvd)
;

[FinalTable]:
LOAD
	*,
	If([DEPOSIT_ID]>=1,'Yes','No') 	as [DepositReceived],
	If([ACT_CLOSE_DT]>1,[ACT_CLOSE_DT],Today())-[Deposit_Receipt_Date] AS [Deposit Claim Age]	
Resident 
	ABC
;

DROP TABLE ABC;

Having a picture of your data model viewer would definitely help although I think the above would do too.

cheers

Lech

[pgloc2020]

Hi Lech,

Thank you so much for your time and letting me know the correct approach. I used the first example you suggested, and it works as needed.

Thank you again.