Table not found in Join multiple resident tables

[43918084]

I have loaded 3 tables where I need to group the fields to create 1 to 1 relationship among the 3 tables.

After that, I need to join the 3 tables.  But when I do join by the 3 resident table, I get error that the last resident table is not found.

Please find below the sample script.  Hope I could get some guidance.  Many thanks in advance

t0:

LOAD

"Arr Lcl Num Ath",

"Impr Stge Aloc Cde" as t0_stage,

sum("Total") as t0_amt

FROM [lib://Loan/Tableau 2020 Dec w cty2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Pivot) group by "Arr Lcl Num Ath","Impr Stge Aloc Cde","Total";

******************************************************************************************************************

t1:

LOAD

"Arr Lcl Num Ath",

"Impr Stge Aloc Cde" as t1_stage,

sum("Total") as t1_amt

FROM [lib://Loan/Tableau 2020 feb w cty2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Pivot) group by "Arr Lcl Num Ath","Impr Stge Aloc Cde","Total";

******************************************************************************************************************

t2:

LOAD

"Arr Lcl Num Ath",

"Impr Stge Aloc Cde" as t2_stage,

sum("Total") as t2_amt

FROM [lib://Loan/Tableau 2020 mar w cty2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Pivot) group by "Arr Lcl Num Ath","Impr Stge Aloc Cde","Total";

*****************************************************************************************************************

NoConcatenate

new:
Load

"Arr Lcl Num Ath",

t0_stage,

t0_Amt

resident t0;

outer join (new):

Load

"Arr Lcl Num Ath",

t1_stage,

t1_Amt

resident t1;

outer join (new):

Load

"Arr Lcl Num Ath",

t2_stage,

t2_Amt

resident t2;

Drop tables t0,t1,t2;

[GaryGiles · Accepted solution]

I'm not sure why your are getting an error that the t2 table is not found.  I was able to get this code to run with the desired results with 1 modication.  For the join statements, you don't need a : at the end.

outer join (new):

should be:

outer join (new)

The other thing I saw was that you are include "Total" in your group by clause.  If you are intending to get one row for each unique instance of "Arr Lcl Num Ath" and "Impr Stge Aloc Cde", you should remove "Total" from the group by clause.

[43918084]

Thank you very much for your kind advice on the wrong group by clause.  No wonder I still get duplicated record after group and join.

Sorry for my typo on the outer join (new):

I actually did not have the ":" in the script.  I did not know what happened.  I have tried loading smaller data and it works.  I hope the same will happen after I add back the full set of data.  

Thank you so so much.  It is really helpful in my struggling to learn this tool