topic Optimization Expression in QlikView

Optimization Expression

Wed, 26 Jun 2013 08:06:29 GMT

Hi all,

I have a chart that consume more ressources memory. I want to optimize this expression.

The Table that contains field KPI_Stock_PTF is a table Island with the rest of the model.

Dimension:

KPI_Stock_PTF

Expression: QTE

if(KPI_Stock_PTF='Total PTF',sum(STK_PTF_DISPO+STK_PTF_DIV+STK_PTF_IMMO+STK_PTF_PER+STK_PTF_PROMO+STK_PTF_QUAR),

if(KPI_Stock_PTF='Flux Tendu',sum({$ <FLUX={'FSDE'}, TYPEMARQUE={'MN','MS'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Flux Stocké',sum({$ <FLUX={'ENT'}, TYPEMARQUE={'MN','MS'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='MDD',sum({$ <TYPEMARQUE-={'MN','MS','MP-EXC'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Exclus',sum({$ <TYPEMARQUE={'MP-EXC'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Consommable',sum({$ <ID_FOUR={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Quarantaine',sum(STK_PTF_QUAR),

if(KPI_Stock_PTF='Promotion',sum(STK_PTF_PROMO),

if(KPI_Stock_PTF='Divers',sum(STK_PTF_PER+STK_PTF_IMMO+STK_PTF_DIV)

)))))))))

Is that more optimize to put into the script and how ?

Thanks for your help.

Re: Optimization Expression

Anonymous — Wed, 26 Jun 2013 08:18:17 GMT

I also build KPIs like this, where the table containing the dimensions is not connected to the rest of the datamodel. I've not seen any optimisation issues with it yet.

Perhaps comment out the various lines to see whether it is one or more of these KPIs that is specifically causing you performance problems. Are any of these KPIs returning an unfiltered/global result, suggesting a link is broken and the calculaton engine is taking time to sum up all of a fields the records.

Something else to watch for is where the fields used in a KPI's calculation are spread across too many links in the datamodel. For instance, for the Flux Tendu KPI, are FLUX TYPEMARQUE, ID_FOUR and STK_PTF_DISPO spread across the datamodel, or occuring on adjacent tables. QlikView should be able to handle spead out data of course, but may take longer to associate it.

Jonathan

Re: Optimization Expression

Gysbert_Wassenaar — Wed, 26 Jun 2013 09:03:25 GMT

You could try a pick-match combination instead of the nested if's.

pick(match(KPI_Stock_PTF, 'Total' ,'Flux Tendu',..etc ,'Divers'), sum_expression_for_total_here, sum_expression_for_fluxtendu_here, ...etc, sum_expression_for_divers_here)

Re: Optimization Expression

Wed, 26 Jun 2013 12:08:01 GMT

Thank Jonathan.

Thank Gysbert for your expression but the memoy (KB) is the same. If function or Pikck function : 6128 KB.