Expression to Script

sona_sa — Tue, 05 Mar 2019 08:44:19 GMT

Hi I have a table, Contain the fields--> [Reporting Month], RAG, Numerator, Denominator, Ratio, Metric, KPI, Actual Value and Matric Value.

By these are the fileld I have created an expression and Now I ant to convert that expression in Script.

There is some set analysis features are available in expression label.

So can any one help me to convert the same expression to script. Below is my expression :

If(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}Len(RAG2)) = 0, Null(),
IF(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}[Metric Value]) = '%',

IF(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))='ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))='PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))=NULL()
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = 'ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = 'PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = NULL()
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = 'ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = 'PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = NULL(),only({$<[Reporting Month]={"$(vCurrentMonthM0)"},
RAG2 = >}[Ratio2]),
if((GetSelectedCount([Reporting Month]) >=1 and GetSelectedCount(Country) >=1),
(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],
if(frac([Numerator2])<> 0, num(Numerator2,'###.##'),NUM(Numerator2 ))))) & ' / ' &
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],
if(frac([Denominator2])<> 0, num(Denominator2,'###.##'),NUM(Denominator2))))) & Chr(13) & '----------' & Chr(13) &
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%')))), NULL())),
if(Ref = 'KPI 53',
only({<[Reporting Month]={"$(vCurrentMonthM0)"}, [Metric Ref] = {'ABC 5'},RAG2 >}SubField(KPI53_Threshold,'|',1)&' (M)'& '+' & SubField(KPI53_Threshold,'|',2) & ' (HVH)'& Chr(13) & '----------' & Chr(13) & [Actual Value2] ),
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 >}[Actual Value2]))))

Can anyone help me on this.

Regards,

Re: Expression to Script

Anil_Babu_Samineni — Tue, 05 Mar 2019 10:06:20 GMT

What is the purpose, you want to move that to script?

Re: Expression to Script

sona_sa — Tue, 05 Mar 2019 10:11:31 GMT

Yes. Can you look into this. Variable - vCurrentMonthM0 --> =DATE(AddMonths(Max([Reporting Month]),0),'MMM-YY') Can you convert the expression to simple script. All fields are available in single table. Regards, AS

Re: Expression to Script

Anil_Babu_Samineni — Tue, 05 Mar 2019 10:22:12 GMT

May be like below?

Table:
Load [Reporting Month] From Table;

Max:
Load Max([Reporting Month]) as MaxDate
Resident Table;

Let vMax = Peek('Max', 0, 'MaxDate');

Drop Table Max;

Left Join(Table)
Load [Reporting Month],
DATE(AddMonths($(vMax),0),'MMM-YY') as vCurrentMonthM0
Resident Table;

Re: Expression to Script

sona_sa — Tue, 05 Mar 2019 11:26:41 GMT

Got it. But there is some more challenges is there for set analysis. Can you help me on this.

topic Re: Expression to Script in QlikView

Expression to Script

Re: Expression to Script

Re: Expression to Script

Re: Expression to Script

Re: Expression to Script