topic Re: Limit the rows in Pivot table in QlikView

Limit the rows in Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am facing one performance issue in Pivot table:

I am having around 85000 rows in my pivot table where i am using few dimensions and writing 2-3 expressions also but performance is very slow. Can we restrict pivot table data like if i declare a variable vChartLimit and giving vChartLimit=1000 then my pivot table should give me 1000 rows only(You can say top 1000 rows)

Is there any way like putting any condition i can solve this problem??

In straight table we can go in property and can select max rows= 100 or 1000 can limit the data but in pivot table i don't know.

Please advice.

Thanks

Pushkar

Re: Limit the rows in Pivot table

ahaahaaha — Sun, 05 Mar 2017 04:42:03 GMT

Hi Pushkar,

Probably will use all table expressions

If(RowNo() < 1000, [Your Expression]),

where 1000 - the number of rows to display.

Regards,

Andrey

Re: Limit the rows in Pivot table

Anonymous — Sun, 05 Mar 2017 07:23:59 GMT

a simple way is to do a filter on your pivot table first.

just like how you select a item inside a listbox and your pivot table gets filtered.

we can do simulate the filter on your pivot table elegantly by going to document properties->trigger tab -> document trigger event -> select onopen then add action -> action type is selection-> selection in field and add in your condition

Re: Limit the rows in Pivot table

ramasaisaksoft — Mon, 06 Mar 2017 07:31:18 GMT

Hi Pushkar,

what is your issue? Performance issue or Data Restriction issue?

If it is Performance:-Even though you restrict the data (1000) in Pivot table it won't increase your performance so you need to do your calculation at script level or in Variable and call those field/Variable directly in Expression so performance will increase.

If it is Data Restriction:-

Based on what criteria you want to filter the rows to 10? Largest Sales?

You could create a key in your script like

LOAD Format,

StoreName,

Reason,

Item,

AutonumberHash256(Format, StoreName,Reason, Item) as PivotKey,

...

Then, in your chart, modify your sales expression to

=Sum( {<PivotKey = {"=Rank(Sum(Sales))<=10"}>} Sales)

Maybe have a look also into:

QlikView Blog Q-Tip #5 – Show Top Performers. Common but not too trivial. | Natural Synergies

(or)

You have to use rank() function possibly in combination with aggr(). Somehting like:

=Aggr( If( Rank(Sum(Sales))<1001, Country), Country) , in calculated dimension. Then check 'Supress When Value is Null' in the dimension tab.

If you feel you got solution ,please close the Thread by Clicking "Correct Answer"