topic Re: Pivot Table vs Straight Table (Performance) in QlikView

Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 09:04:58 GMT

Hi Experts, I have read somewhere online that Pivot table performance is slow. It is not recommended to use pivot table in application having large amount of data. I very curious to know about internal working of pivot table and why It is not recommended. Thanks In Advance, Vivek

Re: Pivot Table vs Straight Table (Performance)

MK_QSL — Fri, 12 Sep 2014 09:20:25 GMT

Obviously If you compare Pivot Table vs Straight Table... Pivot Table is giving low performance due to arranging Row according to Columns...But use of both is depending upon your requirements...

Consider that you have sales data and user want to see Monthly Data for last 3 Years.. the better option here is the dimension Year, Month and Expression Sum(Sales)... Month can be dragged to the top to make it as a column....

But the same can be achieved using Straight but in this case, you have to prepare 12 different SUM(Sales) statements....not viable option....!

(I have tried my best to describe, but other GURUs can describe in better way... I am not much good in teaching )

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 09:22:52 GMT

DIFFERENCE BETWEEN STRAIGHT AND PIVOT TABLE
	PIVOT TABLE	STRAIGHT TABLE
ASCENDING/DESCENDING CHANGE OPTION ON COLUMNS	NO	YES
SEARCHING/DROPDOWN SELECT ON COLUMN	YES	YES
COLAPSE/EXPAND COLUMN	YES	NO
GROUPING	YES	NO
DRAG AND DROP COLUMN	YES	YES
SUB TOTAL	YES	NO
TOTAL	YES	YES
DRAG EXPRESSION	NO	YES
HIDE COLUMN OPTION PERMANENTLY BUT INTERNALLY WORK	NO	YES
TOTAL MODE (NO TOTAL, EXPRESSION TOTAL, SUM OF ROWS) OPTION ENABLE	NO	YES

A pivot table is better at grouping: you can easily see which group a specific row belongs to, and a group can have a subtotal. You can also display a pivot table like a cross table (one or several horizontal dimensions). But when you sort a pivot table, you have to sort it first according to the first dimension, then according to the next, etc. You cannot sort it any way you want.

A straight table is better at sorting than a pivot table: you can sort it according to any column. But it is not so good at grouping. Subtotals are not possible, for instance.

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 09:29:33 GMT

Hi,

Thanks all for your quick reply

You both are correct

but i want to know more about internal working of Pivot table and straight table.....

why pivot table is slower as compared to straight table...

Re: Pivot Table vs Straight Table (Performance)

jagan — Fri, 12 Sep 2014 09:32:59 GMT

Hi Vivek,

Straight table will have better performance over Pivot. If you are pivoting the column or you want grouping then you have to use Pivot. If you are able to manage your client with Straight table then use it.

Regards,

Jagan.

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 10:14:43 GMT

Since, Qlikview read the data row wise. and in pivote our data are in group.

so this may be a reason of performance. but i'm not sure.

Re: Pivot Table vs Straight Table (Performance)

vikasmahajan — Fri, 12 Sep 2014 10:17:15 GMT

Straight Table is better option simple and easy to use.

Vikas

Re: Pivot Table vs Straight Table (Performance)

Colin-Albert — Fri, 12 Sep 2014 10:27:56 GMT

Sub-totals are possible in straight tables using the Dimension Limits tab.

Though you may want to disable interactive sorting if using sub-totals as changing the sort order will alter the results.

Also pivot tables do not support all options for mini-charts.

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 10:34:44 GMT

All has been already said by my fellow members. But since you are more likely to understand what is happening internally between both objects. This information/research is very limited to few geeks out here(you need find them).

But to sum up, I would say Pivot and Straight table have their own situations where they are best fit.

So,for most of the common situation, straight table give better performance.

Thanks,

Angad

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 10:53:35 GMT

Hi Jagan ,

Yes I am completely agree with you . It is not a client requirement.

Just for my knowledge I want to know why Pivot have better performance.

Regards,

Vivek

Re: Pivot Table vs Straight Table (Performance)

Fri, 12 Sep 2014 10:58:45 GMT

Hi Vishwarajan,

It may be the reason.

Lets wait for more opinion from qlikview Expert.

Regards,

Vivek