topic Re: Use of variables in QlikView

Use of variables

Anonymous — Tue, 22 Aug 2017 13:59:50 GMT

Hi all, beginner here:

My situation: one huge table (which takes my system about 5 minutes to calculate O.o)

Some of the columns which are calcualted have the same code in their calculation:

Example:

Column 1: COUNT( DISTINCT x)) -5

Column 2: COUNT (DISTINCT x)) * 2

Column 3: COUNT (DISTINCT x)) * 10,5

Column 4: COUNT (DISTINCT x)) / 320

Column 5: Count ( x )

(The calculations are not that simple, but I'm sure you get the gist)

My question:

a) Is it worth it (Performance wise!) to add a variable for same code parts => "COUNT ( DISTINCT x))" ?

b) Can Qlikview cache only results of complete calculations, or also results of sub-calculations?

c) Can QlikView cache calculated variables?

d) how long does QlikView cache them? Until reload or only until restart of the application?

With a variable the columns would be calculated:

// varX = COUNT(DISTINCT x))

Column 1: $(varX) -5

Column 2: $(varX) *2

Column 3: $(varX) * 10,5

Column 4: $(varX) &320

Column 5: Count ( x )

Regards

Vic

Re: Use of variables

sunny_talwar — Tue, 22 Aug 2017 15:48:46 GMT

Look here:

improving QlikView performance‌

It seems that from troyansky‌ response that variable in fact help improve performance.

Re: Use of variables

Peter_Cammaert — Tue, 22 Aug 2017 18:26:40 GMT

An alternative (local to a single object) approach could be to create a hidden column containing expression Count(Distinct x), naming it (use the label field for that) and referring to that expression label in all other visible expression columns except the last one. Let's say you called that column CntDistX, then the others would get the following expressions:

Column 1 (hidden): Count(DISTINCT x) // Called 'CntDistX'

Column 2: CntDistX - 5

Column 3: CntDistX * 2

Column 4: CntDistX * 10.5 // constants use US separators

Column 5: CntDistX / 320

Column 6: Count( x )

This is easy to create and you will detect any performance improvements immediately.

Re: Use of variables

Oleg_Troyansky — Tue, 22 Aug 2017 19:56:55 GMT

I'd say in this case using variables is unlikely to improve performance, because even with the variables, these expressions will be treated as separate formulas, so no caching can be used...

I'd second Peter's suggestion to add a hidden column that calculates the distinct count of x - that should indeed help in this case.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: Use of variables

Anonymous — Wed, 23 Aug 2017 06:10:36 GMT

Thank you very much! This is great advice