topic Performance help in QlikView

Performance help

gauthamchilled — Mon, 19 Oct 2015 03:23:12 GMT

QuarterYear,HalfYear,MYear and Year fields are from calendar table.
SelectionPeriod is a data island for period selection(created for drop down listbox)

I used the following as calculated dimension in most of the charts,
=if([SelectionPeriod]='Quarter',QuarterYear,
if([SelectionPeriod]='Half Year',HalfYear,
if([SelectionPeriod]='Year to Date',MYear,
if([SelectionPeriod]='Full Year', Year))))

I suspect this may cause performance issue, Can anyone suggest how can i replace either in the script or as set analysis or in the variable?

Re: Performance help

SreeniJD — Mon, 19 Oct 2015 03:30:55 GMT

Instead of nested IFs, use If(Match(SelectionPeriod]=('Quarter','Half Year','Year to Date','Full Year'),...................)

HTH

Sreeni

Re: Performance help

gauthamchilled — Mon, 19 Oct 2015 03:33:10 GMT

Sreeni,

I already tried, I don't see a much improvement in performance between match and nested if. Any other suggestions or help from anyone?

Re: Performance help

SreeniJD — Mon, 19 Oct 2015 03:36:57 GMT

You might use Pick(WildMatch()) as well to improve performance... Just try that

Hope this will help

Sreeni

Re: Performance help

qlikviewwizard — Mon, 19 Oct 2015 03:44:27 GMT

Hi,

Try like this in script:

LOAD *,

if(wildmatch([SelectionPeriod],'Quarter') >=1 ,QuarterYear,

if(wildmatch([SelectionPeriod],'Half Year') >=1 ,HalfYear,

if(wildmatch([SelectionPeriod],'Year to Date') >=1 ,MYear,

if(wildmatch([SelectionPeriod],'Full Year') >=1 ,Year))));

Data:

Your script;

Re: Performance help

settu_periasamy — Mon, 19 Oct 2015 03:56:19 GMT

Hi Gautham,

May be you can try with "Enable Conditional". Like Instead of Calculated Dimension, Put Your needed fields in Dimension, Then you can enable it based on your Selection Period.

Re: Performance help

gauthamchilled — Mon, 19 Oct 2015 04:57:04 GMT

Thanks for all your responses. Is there any way that I can handle this in script?

Re: Performance help

jonathandienst — Mon, 19 Oct 2015 05:04:10 GMT

That dimension should not cause significant performance problems as it does not need to be calculated on a row by row basis.There should be no reason to handle this in script.

That said, I would do this with a cyclic dimension -- From the Dimension tab (or Document Properties), click Groups, and add a cyclic group containing the fields in your dimension expression and use the cyclic as the chart/table dimension.

Re: Performance help

qlikviewwizard — Mon, 19 Oct 2015 05:20:11 GMT

Did you try my logic?

Re: Performance help

jagan — Mon, 19 Oct 2015 05:27:37 GMT

Hi Gautam,

Do you have performance issues only in this particular chart or overall dashboard? I think this expression won't have performance issues. What is the volume of the data? What is the size of Qlikview file?

You can also try Cyclic Groups or Enable conditional options, did you tried that?

Regards,

Jagan.

Re: Performance help

rwunderlich — Mon, 19 Oct 2015 10:15:11 GMT

Yes, a data island with the if() could be a significant performance problem. A couple of solutions:

Option 1. Create a variable named vChoosePeriod and move the if() there. It should return a String of the fieldname and be defined with a leading "="/

=if([SelectionPeriod]='Quarter','QuarterYear',

if([SelectionPeriod]='Half Year','HalfYear',

if([SelectionPeriod]='Year to Date','MYear',

if([SelectionPeriod]='Full Year', 'Year'))))

Then your calculated Dim is:

=vChoosePeriod

Option 2. Link the UI Selection to the Calendar fieldname:

UI_Period:

LOAD * INLINE [

SelectionPeriod, SelectionField

Quarter,QuarterYear

Half Year,HalfYear

Year to Date,MYear,

];

Then your calculated Dim is:

=$(=SelectionField)

For more, see

How to Choose an Expression | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com