topic Re: Optimizing Expression in QlikView

Optimizing Expression

chhavi376 — Tue, 17 Apr 2018 13:26:30 GMT

Hi All,

I have a situation.

I am attaching a qlikview file here:

I have 1 table and 2 filters in my original app(Just like the sample I am attaching here)

The problem is that the expression (specified below) is taking a lot of time to calculate when the number of patients increases.

=Pick(Match('['&Type&']',$(=Concat(DISTINCT chr(39)&'['&Type&']'&chr(39), ','))),

$(=Concat(DISTINCT 'Sum($(vfilter)[' &Type&'])' ,','))

)

The application i am developing is in qliksense.

I have made a Qlikview app and attached here just to give a reference.

It would be great if anyone has any other way to optimize this.

Regards,

Chhavi

Re: Optimizing Expression

marcus_sommer — Tue, 17 Apr 2018 15:09:25 GMT

It's not quite clear what the aim behind your expression is. Do I understand it right that within the real data there could be dozens or maybe even hundreds of measures which are included in the pick(match())? If yes a different kind of the datamodel by using The Crosstable Load to transform your tables might be useful. Also you are using some kind of linktable between Type and the measures which is rather adverse from a performance point of view.

- Marcus

Re: Optimizing Expression

chhavi376 — Wed, 18 Apr 2018 05:41:35 GMT

Hey Marcus,

Thanks for responding.

Please refer to the app i have attached.

I cannot pivot the data because of the following requirement:

The table gives us the sum marked above.

if we select 'include' filter, the following should be the result:

if we select 'exclude' filter, the following should be the result:

To achieve this, a variable (vfilter) has been created which has the set analysis to be used in the expession.

regards,

Chhavi

Re: Optimizing Expression

marcus_sommer — Wed, 18 Apr 2018 07:09:02 GMT

It's further not really clear for me how the filters should be applied and how the results should be look like.

Do the include/exclude numbers mean that a fieldvalue of 1 should be included/excluded or is the sum of the fieldvalues meant? Further if they should be applied within set analysis conditions why are they linked with the data? Therefore the intended output is unclear, too and deduced from this why such a pick(match()) to assign the appropriate expression is used?

- Marcus