topic Re: Moving complex expressions in qlikview script in QlikView

Moving complex expressions in qlikview script

kishorj1982 — Tue, 02 May 2017 09:19:33 GMT

Hi all,

PFA.

Can you please suggest how should i optimize the script.It is taking too much time to open the app in my origional app need to move complex calculations in back end.

I have attached reduced version now

Thanks

Re: Moving complex expressions in qlikview script

kishorj1982 — Wed, 03 May 2017 06:01:38 GMT

Hi all,

Can you please suggest on this.

Thanks

Re: Moving complex expressions in qlikview script

Anil_Babu_Samineni — Wed, 03 May 2017 06:37:18 GMT

I am not sure, Whether I can help you from your script. Script seems okay to me. May be delete comment rows then check and can we know the noofrows on your Fields

Re: Moving complex expressions in qlikview script

kishorj1982 — Wed, 03 May 2017 08:23:54 GMT

Hi,

I am asking about chart expressions need to move some part in backend because chart performance is very slow.

Thanks

Re: Moving complex expressions in qlikview script

prieper — Wed, 03 May 2017 08:37:51 GMT

First: would place the calculated dimensions into the script.

Second: formatting is usually not necessary in the expression, but might be done under Properties->Number

Third: expressions with IF(Match(Flag....) are not necessary, you may also write SUM(Value) * (Flag....)

Fourth: your third expression is mixing aggregated with fixed values (one not part of the database) - please check

HTH Peter

Re: Moving complex expressions in qlikview script

kishorj1982 — Wed, 03 May 2017 11:04:59 GMT

Hi Thanks for your valuable response.

Can you please show me in my script.

Thanks

Re: Moving complex expressions in qlikview script

prieper — Wed, 03 May 2017 13:37:15 GMT

You had it nearly done in line 140:

Credit_Evaluation:

LOAD

if(Match(CRD_Limit_Status, 'Over Credit Limit', 'Under Credit Limit'), Cust#_Name) AS Header1,

if(Match(CRD_Limit_Status, 'Over Credit Limit'), Cust#_Name) AS Header2;

LOAD

ApplyMap('Credit_Eval_NAE_Map', CustomerNumber) as CRD_Limit_Status

Resident Credit_Evaluation_Temp;

Peter

Re: Moving complex expressions in qlikview script

kishorj1982 — Wed, 03 May 2017 14:06:55 GMT

Hi Peter,

Can you please suggest what will be the exrpession. I am bit confusing now.

Thanks

Re: Moving complex expressions in qlikview script

prieper — Wed, 03 May 2017 14:18:01 GMT

You had as expression:

if(Match(_Flag_LOB_0405, 1),Round(Sum(MBIA),0))

This can only be calculated, if there are dimensions sharing the same flag.

This would be a working formula:

SUM(IF(MATCH(_Flag_LOB_0405, 1), MBIA))

Faster would be

SUM(MBIA * _Flag_LOB_0405)

Similar with other expressions then.

Peter

Re: Moving complex expressions in qlikview script

kishorj1982 — Sun, 07 May 2017 08:22:41 GMT

Thanks peter for your valuable response.

Can you please suggest what would be the optimize expression for below,

if(Dimensionality () <> 0,

if(

Match(_Flag_LOB_010203, 1),

Round(CRD_Limit_USD - Sum(NAE) + Sum(TotalReserve_USD)),

//Round(CRD_Limit_USD - Aggr(Sum(NAE), CustomerNumber) + Aggr(Sum(TotalReserve_USD), CustomerNumber)),

if(

Match(_Flag_LOB_0405, 1),

Round(CRD_Limit_USD - $(vTotalOpenAR) - Sum(MBIA)),

Round(CRD_Limit_USD - $(vTotalOpenAR)

))

)

Re: Moving complex expressions in qlikview script

kishorj1982 — Mon, 08 May 2017 05:37:01 GMT

Hi all,

Can you please suggest on this.

I want to remove if else in expressions

thanks

Re: Moving complex expressions in qlikview script

prieper — Mon, 08 May 2017 07:04:07 GMT

MATCH() delivers the position of the result, using MATCH for a flagged field, like

MATCH(MyField, 1)

will deliver either "1", in case that the content of this field is 1 and "0" for all other entries.

So any product calculated will result in either the full amount or in 0.

In the above calculation, where you use nested IFs, see not much possibility, without knowing your data and the background of the calculation.

For better readability would remove the formatting and either do the rounding in the script or in the number-settings.

Peter

Re: Moving complex expressions in qlikview script

kishorj1982 — Mon, 08 May 2017 07:30:22 GMT

Thanks,

what would be the alternative for

if( Dimensionality () <> 0,Round(sum(Total_write_offs_USD)/FX_CONV_RT)/Metric)

For hiding total value in first row for specific column.

Thanks

Re: Moving complex expressions in qlikview script

prieper — Mon, 08 May 2017 08:13:38 GMT

if "Metric" is a variable, in which you - or the users - may steer the output,

see no alternative.

Peter

Re: Moving complex expressions in qlikview script

kishorj1982 — Mon, 08 May 2017 11:06:31 GMT

shall i use pick match for below.If so please suggest.

if( Dimensionality () <> 0,Round(sum(Total_write_offs_USD)

Re: Moving complex expressions in qlikview script

prieper — Mon, 08 May 2017 12:08:34 GMT

no - I do not see an advantage in doing so.

edit: spelling

Re: Moving complex expressions in qlikview script

kishorj1982 — Fri, 12 May 2017 05:19:31 GMT

Hi Peter.

I have 1,00,000+ records in my table.

For showing results on access point it is taking too much time.

Please suggest.

Thanks

Re: Moving complex expressions in qlikview script

prieper — Fri, 12 May 2017 09:36:44 GMT

I can't help further, sorry

Peter