Use of variables

[Anonymous]

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

[sunny_talwar]

Look here:

improving QlikView performance‌

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

[Peter_Cammaert · Accepted solution]

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.

[Oleg_Troyansky]

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!

[Anonymous]

Thank you very much! This is great advice