topic Re: Performance help in QlikView

Performance help

gauthamchilled — Mon, 19 Oct 2015 06:58:04 GMT

I'm using the below expression in variable and using in many charts, can anyone help in simplifying. records are about 5 millions. Help me in tuning performance please .

PFA

Re: Performance help

avinashelite — Mon, 19 Oct 2015 07:03:37 GMT

I didn't get your exact requirement , but try like this :

=if([Reportflag]='y' and Acctype=TypeName,-value,value)

Re: Performance help

phaneendra_kunc — Mon, 19 Oct 2015 07:16:02 GMT

Even i dont understand your requirement. And more over is your data model in your original app was built like the one in attached? meaning "Value" being key?

One thing i would suggest is to take that IF logic back to the script if at all possible. If that is not the case i would create some FLAG's in script and use SET ANALYSIS.

It will be nice if you can share more info

Re: Performance help

gauthamchilled — Mon, 19 Oct 2015 07:23:32 GMT

This is the expression I used in most of the charts:

if([Reportflag]='y', -if(Acctype=TypeName,value,-value),if(Acctype=TypeName,value,-value))

Help me to handle this in the script.

Since Acctype and typename are in two different tables, I don't know how to compare and derive the expression in script.

Re: Performance help

avinashelite — Mon, 19 Oct 2015 07:29:29 GMT

if you want to achieve this in the script level you need to do a JOIN , join on 1 million row would not idle because it may recreate lot of data and the size of the file may get increase .

Re: Performance help

phaneendra_kunc — Mon, 19 Oct 2015 07:42:25 GMT

You can use APPLY MAP to create a new field called "new_value" in the script and just use "Sum(new_value)" in front end chart.

TypeMap:

Mapping Load *, 1 as Flag;

Load *Inline

[

TypeName

Type3

Type7

];

LargeTable:

Load *, if(Reportflag='y', if(ApplyMap('TypeMap',Acctype,0) = 1, -1*value, value),if(ApplyMap('TypeMap',Acctype,0) = 1, value, -1*value)) as new_value;

Load * inline

[

Company,Acctype,value,Reportflag

AAA,Type1,100,y

BBB,Type2,140,y

CCC,Type3,270,n

ddd,Type7,156,y

];

Re: Performance help

avinashelite — Mon, 19 Oct 2015 07:47:37 GMT

phaneendra.kunche‌

if we have one to many relationship between the tables than Apply mapping will fail

Re: Performance help

phaneendra_kunc — Mon, 19 Oct 2015 07:54:51 GMT

Even in that case the expression he wrote in the UI will fail as well un less if he includes the combination of dimensions in the chart to get to a single Acoount Type & Type Name for the combination to validate TRUE/FALSE.

Above is one concept where he can minimize the burden on UI to calculate the expression using applymap. There might be more to create flags if there is 1-Many relations in the data. Given the example scenario and lack of requirement at this moment this is the only option i can think of.

I would like to hear your opinion on how to boost the performance on 5Mil dataset. Share if you know any other solution.

Re: Performance help

avinashelite — Mon, 19 Oct 2015 08:02:32 GMT

I agree with your point, without knowing the exact requirement it would be not idle to speak or suggest the solution let's wait for some more info