Load script - Left join resident table

[#1]

Hi Everyone,

I am trying to do a left join in my load script. My syntax appears to be correct to me but I get an error saying 'Table not found' for the table I trying to use as my resident table and the table I am trying to left join to. Here is my code:

SALES_FLAGS_2:

LOAD T_ACCOUNT_WID AS RPT9_ACCOUNT_WID
 ,SUM(T_SALE_PRICE_GBP) AS RPT9_T_SALE_GBP
 ,SUM(T_SALE_PRICE_USD) AS RPT9_T_SALE_USD
 ,SUM(T_SALE_PRICE_EUR) AS RPT9_T_SALE_EUR
 ,MIN(T_SALE_START_DATE) AS RPT9_FIRST_PURCHASE_DT
RESIDENT HI_TMP_12_TRANS_FINAL_GRPS
WHERE T_SALE_START_DATE>=ADDYEARS($(vREPORT_YTD), -2)
AND RECORD_TYPE='BUYER'
GROUP BY T_ACCOUNT_WID;


REGISTRATION_FLAGS_3:
LOAD DISTINCT ACCOUNT_WID AS RPT9_ACCOUNT_WID
 ,SALE_WID AS RPT9_SALE_WID
 ,R_SALE_START_DATE AS RPT9_R_SALE_START_DATE
RESIDENT HI_REGISTRATIONS
WHERE R_FIRST_REG_DT >= ADDYEARS($(vREPORT_YTD), -2)
AND R_SALE_START_DATE > R_FIRST_REG_DT;

REGISTRATION_FLAGS_4:
LOAD RPT9_ACCOUNT_WID
 ,RPT9_SALE_WID
 ,RPT9_R_SALE_START_DATE
RESIDENT REGISTRATION_FLAGS_3;

LEFT JOIN (REGISTRATION_FLAGS_4)
LOAD RPT9_ACCOUNT_WID
 ,RPT9_FIRST_PURCHASE_DT
RESIDENT SALES_FLAGS_2;

Any help would be greatly appreciated!!

Thanks

Gareth

[rwunderlich · Accepted solution]

REGISTRATION_FLAGS_4 has the same Fields as REGISTRATION_FLAGS_3 so it is getting auto concatenated to REGISTRATION_FLAGS_3. If you don't want the concatenation, add NOCONCATENATE to the load of REGISTRATION_FLAGS_4.

[Anonymous]

Gareth,
The table REGISTRATION_FLAGS_4 was never created - it has exactly the same set of fields as REGISTRATION_FLAGS_3, so it simply was auto-concatenated to REGISTRATION_FLAGS_3.
(Not sure why you need the REGISTRATION_FLAGS_4 at all since it is exactly the same REGISTRATION_FLAGS_3. Maybe you need just to left join to REGISTRATION_FLAGS_3.)

[#4]

Thanks very much,!