Variable set in Script editor cannot show as Measure in table

43918084 — Tue, 30 Nov 2021 18:17:33 GMT

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) = '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) = '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))))));

Re: Variable set in Script editor cannot show as Measure in table

GaryGiles — Fri, 11 Jun 2021 12:07:50 GMT

Try defining the variable as a string. Note that you will need to use 2 single quotes within the string. Like this:

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))))))';

Then, in the measure expression, $(Mov_GCV).

Re: Variable set in Script editor cannot show as Measure in table

43918084 — Fri, 11 Jun 2021 13:02:43 GMT

Thanks a lot for your wise advice. I really appreciate it. 🙂😀

topic Variable set in Script editor cannot show as Measure in table in App Development

Variable set in Script editor cannot show as Measure in table

Re: Variable set in Script editor cannot show as Measure in table

Re: Variable set in Script editor cannot show as Measure in table