topic Re: Background color performance on million rows in QlikView

Background color performance on million rows

Anonymous — Mon, 27 Apr 2015 12:02:49 GMT

Dear Community!

I use a straight table or pivot with approx. 400 million rows and 8 columns, where each is a dimension and get a backgorund color in case of an complex expression. As one of you might expect, if there is no filter it will be terrible slow and consuming a lot of ram.

It would be enough, if the expression will be active, if one important filter is active, so that the perfomance will be better.

I tried for this following expression :

If(GetSelectedCount(FILTER_FIELD)>=1,if(COMPLEX EXPRESSION),RGB(255,255,204))

But still the perfomance is terrible, as i think he still analyses every row.

Any idea how can i tell him to start the expression if an filter is active, or the result row number will be smaller than a specific nr.

Or have someone another ideas for a better perfomance, with such a amount of data and coloring filter results?

Re: Background color performance on million rows

Mon, 27 Apr 2015 12:13:16 GMT

Hi,

Maybe your 'Complex Expression' can be simplified.

Can you show us?

Re: Background color performance on million rows

Anonymous — Mon, 27 Apr 2015 13:47:42 GMT

Hi Fernando, see below:

if(match(mid(FIELD_VALUE,1,6),$(=(CONCAT(DISTINCT(chr(39)&FIELDSVALUE&chr(39)),', ')))

for more info about this expression see following discussion: https://community.qlik.com/message/761137

Re: Background color performance on million rows

Anonymous — Mon, 27 Apr 2015 13:54:48 GMT

I don't believe that there is a practical use of a table with 400 M rows. Or even with 10 thousand rows. My recommendation - conditionally enable calculations only if the number of rows < than some reasonable number, e.g. 10,000.

Re: Background color performance on million rows

Anonymous — Mon, 27 Apr 2015 14:00:11 GMT

Hi Michael,

i share your opinion, but how can i "conditionally enable calculations only if the number of rows < x" ??

Re: Background color performance on million rows

Anonymous — Mon, 27 Apr 2015 14:05:44 GMT

In the chart properties, tab general, find Calculation condition. Write your condition there. I can't guess how to calculate number of rows in your case. Typically there is a field that identifies the row, e.g. some ID. In this case:

count(distinct ID)<10000

Next, you can add some flexibility. Create a variable vMaxRows, and add an input box with this variable. Use it in calculated conditions:

count(distinct ID)<vMaxRows

Besides, to help your users, modify the message "calculation condition unfulfilled" to a something more helpful, e.g.:

Re: Background color performance on million rows

Anonymous — Tue, 28 Apr 2015 08:08:29 GMT

Great Michael,

that helped me a lot!

Actually i have 8 Dimensions inside a pivot, and count the rows to display results that are equal or smaller than 100

count(aggr(DIM1,DIM2,DIM3,DIM4,DIM5,DIM6,DIM7,DIM8))<=100

Is there a better way to achive this? (tried already RowNo & NoofRows but they are not working and slower)

Re: Background color performance on million rows

Anonymous — Tue, 28 Apr 2015 11:28:31 GMT

Your condition looks fine. Just to keep in mind that it count also the rows where all expressions are 0, and if you hide them (by "suppress zero-values"), the count will be off. If you don suppress zero-values, you count is good.

Re: Background color performance on million rows

Anonymous — Tue, 28 Apr 2015 12:10:44 GMT

Maybe for any one else interesting : For a better perfomance than counting

set that one or more meaningfull filter(s) need to be choosen, before the calculation starts.

I achieved that using : GetSelectedCount(NEEDED_FILTER_FIELD)>=1

Inside the error message we set the names of the fields that are necessary for the object!

Works really perfomant now on a huge data set.