Unable to find field from source resident table

[43918084]

I have created the below table "AI" as resident table. I want to create another table to do calculation from table "AI". But somehow it cannot find the fields such as t1_Stage, t0_Stage etc in the new table.

Also, when I use Sum in the loading script, do I have to group by all dimension field in the table script?

Hope I can get some expert advice

AI:

LOAD

Grca Account&'_'&"Detail Product Code"&'_'&"Ip Lcl Ref Cde Ith" as primkey,

if(isnum("Arr Lcl Num Ath"), num("Arr Lcl Num Ath",'###################'),text("Arr Lcl Num Ath")) as ALN,

"Ip Lcl Ref Cde Ith",

if(len("Ip Lcl Ref Cde Ith")<=1, 'null','') as nullchk,

"Grca Account",

"Ip Shrt Name Ith",

"Period Date 42" as t0_period,

"Grca Custom3" as NACE,

"Grca Custom6" as t0_stage,

"Grca Primary Account",

"Detail Product Code",

'On' as "On/Off",

sum("Ytd Bal")/1000 as t0_GCV_HKD000

FROM [lib://QS_Dev/AI80010_6_LQ-2.xlsx]

(ooxml, embedded labels, table is data)

WHERE ([Ytd Bal]>1 or [Ytd Bal]<-1)

Group by "Arr Lcl Num Ath","Ip Lcl Ref Cde Ith","Ip Shrt Name Ith","Detail Product Code","Grca Account",

Period Date 42,"Grca Primary Account","Grca Custom3","Grca Custom6";

//***********************************************************************************

outer join(AI)

LOAD

Grca Account&'_'&"Detail Product Code"&'_'&"Ip Lcl Ref Cde Ith" as primkey,

'On' as "On/Off",

if(isnum("Arr Lcl Num Ath"), num("Arr Lcl Num Ath",'###################'),text("Arr Lcl Num Ath")) as ALN,

"Ip Lcl Ref Cde Ith",

if(len("Ip Lcl Ref Cde Ith")<=1, 'null','') as nullchk,

"Grca Account",

"Ip Shrt Name Ith",

"Period Date 42" as t1_period,

"Grca Custom3" as NACE,

"Grca Custom6" as t1_stage,

"Grca Primary Account",

"Detail Product Code",

sum("Ytd Bal")/1000 as t1_GCV_HKD000

FROM [lib://QS_Dev/AI80010_6_CQ.xlsx]

(ooxml, embedded labels, table is data)

WHERE ([Ytd Bal]>1 or [Ytd Bal]<-1)

Group by "Arr Lcl Num Ath","Ip Lcl Ref Cde Ith","Ip Shrt Name Ith","Detail Product Code","Grca Account",

Period Date 42,"Grca Primary Account","Grca Custom3","Grca Custom6";

new:
Load
"Grca Account"&'_'&"Detail Product Code"&'_'&"Ip Lcl Ref Cde Ith" as primkey,
"Grca Account",
t1_stage, (I got error that t1_stage is not found)
t0_stage, (I got error that t0_stage is not found)

"Detail Product Code",
"Ip Lcl Ref Cde Ith",

if((Sum(t0_GCV_HKD000)=0 and sum(t1_GCV_HKD000)<>0),'New',
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)=0),'Repay',
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)-sum(t0_GCV_HKD000)>0),'FL',
if(right(t1_stage,1)>right(t0_stage,1),'Dn_Gr',
if(right(t1_stage,1)<right(t0_stage,1),'Up_Gr',
if((Sum(t0_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)<>0 and sum(t1_GCV_HKD000)-sum(t0_GCV_HKD000)<0),'Repay',
1))))))
as MvtType,

if(right(t1_stage,1)<>right(t0_stage,1),sum(t1_GCV_HKD000),sum(t1_GCV_HKD000)-sum(t0_GCV_HKD000)) as Var

resident AI;

[rubenmarin]

Hi, I don't know why it doesn't show, it should return an error on the first row it there was no rows loaded. You can add an "exit script" sentence after loading AI table to check what it has.

And yes, in the Group by you need to add all fields not used in an aggregation function.

I suppose it's a typo... Period Date 42 on the Group by should be between double quotes or brackets: "Period Date 42"