Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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
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
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.