Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 )
hi
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. |
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...
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.
hi
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.
Straight Table is better option simple and easy to use.
Vikas
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.
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
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