I have tried to create a resident table to do calculation on a Parent table that is a combination of multiple source. Please find below my script and would appreciate some guidance where I have done wrong
[Cust_Bal]:
LOAD Distinct
"Arr Lcl Num Ath",
"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code" as 'map_key',
"Grca Account",
"Global Sic Code",
"Detail Product Code",
[Lcl Prod Cde],
Dual([Ip Lcl Ref Cde Ith], [Ip Lcl Ref Cde Ith]) as 'CustNbr',
Subfield(ApplyMap('Cur_bal_6',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7)) as 'Cur_Stage',
Subfield(ApplyMap('Lst_ECL_5',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) as 'LstECL_Stage',
If(
Subfield(ApplyMap('Cur_bal_6',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE3' and
Subfield(ApplyMap('Lst_bal_6',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE2',
'Downgrade from Stage2 to Stage3'
))))))))) as 'Stage_Move',
Cur_BAL, Lst_Bal
from xxx.xlsx
Load
map_key,
Stage_Move,
Cur_Stage,
Lst_Stage,
IF(Stage_Move = 'New' and Cur_Stage = Lst_Stage,sum(Cur_Bal),
IF(Stage_Move = 'Payoff' and Cur_Stage = Lst_Stage,sum(Lst_Bal)*-1,
IF(Stage_Move = 'Same' and Cur_Stage =Lst_Stage, sum(Cur_Bal-Lst_Bal),
IF(Right(Cur_Stage,1)=Right(Stage_Move,1), sum(Cur_Bal), IF(Right(Lst_Stage,1)=mid(Stage_Move,21,1), sum(Lst_Bal)*-1))))) as Movt_GCV
Resident [Cust_Bal] Group by map_key,
Stage_Move,
Cur_Stage,
Lst_Stage;
