I have loaded below script in QS, followed by a "Let" for calculation of variable. However, when I try to apply the variable in table, it shows "-" for all rows. Hope I could get some guidance.
[Cust_Bal]:
LOAD Distinct
"Arr Lcl Num Ath",
"Arr Lcl Num Ath" & '_' & "Glbl Detail Customer Type Code" as 'map_key',
"Global Sic Code",
"Detail Product Code",
[Lcl Prod Cde],
Dual([Ip Lcl Ref Cde Ith], [Ip Lcl Ref Cde Ith]) as 'CustNbr',
ApplyMap('Cur_bal_910_on',"Arr Lcl Num Ath" ,'#N/A') as 'Cur_PastDue',
Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",0),'|',13) as 'Cur_Bal',
ApplyMap('Lst_bal_910_on',"Arr Lcl Num Ath" ,'#N/A') as 'Lst_PastDue',
Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",0),'|',13) as 'Lst_Bal',
"Ip Shrt Name Ith",
If(IsNull(Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7)),
'New',
If(If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) =Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7),
'Same',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE1' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE2','Upgrade from Stage2 to Stage1',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE1' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE3','Upgrade from Stage3 to Stage1',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE2' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE3', 'Upgrade from Stage3 to Stage2',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE2' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE1', 'Downgrade from Stage1 to Stage2',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE3' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE1', 'Downgrade from Stage1 to Stage3',
If(Subfield(ApplyMap('Cur_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE3' and Subfield(ApplyMap('Lst_bal_6_on',"Arr Lcl Num Ath" & '_' & "Detail Product Code" & '_' & "Glbl Detail Customer Type Code",'#N/A'),'|',7) = 'STAGE2', 'Downgrade from Stage2 to Stage3'
))))))))) as 'Stage_Move',
//FROM [lib://QS_Dev/CrRiskCust.xlsx]
//(ooxml, embedded labels, table is Data);
FROM [lib://QS_Dev/AI80010_6_CQ.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
WHERE ([Ytd Bal]>1 or [Ytd Bal]<-1);
let Mov_GCV = Sum(IF(Stage_Move = 'New',Cur_Bal,
IF(Stage_Move = 'Payoff',Lst_Bal*-1,
IF(Stage_Move = 'Same',Cur_Bal-Lst_Bal,
IF(Right(Cur_Stage,1)=Right(Stage_Move,1), Cur_Bal, IF(Right(Lst_Stage,1)=Right(Stage_Move,1), Lst_Bal*-1))))));
