Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rittermd
Master
Master

Pivot Table Issue

I have an app that has a pivot table on one sheet.  When testing with a small amount of data it worked fine.

It has 4 dimensions and 4 measures.  The measures are really just displaying some data elements.

The data is in a qvd and is about 3M rows now.

If I do not filter the data first the app just displays a circle (for loading) and shoots the memory use on the server up to 97%.  I'm not sure that it ever completes loading.

Is there something that I need to do differently when using a pivot table to make it more efficient?  Is there an alternative to a pivot table that can give me the same output.  Or is this a bug.

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Its hard to give useful advice without some knowledge of the expressions and your data model. It could be one or more of several factors, such as

  • Inefficient expression logic
    • nested ifs
    • sum(if()) type operations
    • complex logic with pick(matches()) or other complex structures
  • Data model problems
    • broken or incomplete associations
    • overly deep nesting
    • multiple fact table(s) and large, high cardinality link tables
    • complex compound keys or synthetic key "runaway"

A lot of these problems are mitigated by making selections, but as the model gets larger, the problems will become more severe.

If you provide a small sample with some data reflecting the model structure and some chart objects with the offending expressions, it will be possible to provide more detailed assistance. Of course the small model may not reflect the performance problems, but it will help in looking for possible causes.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable

One more possibility - each of the pivot table dimension has a very large number of the distinct values.  So, it results in an enormous number of cells in the table.

rittermd
Master
Master
Author

If that is the case how can I get around it?

My dimensions are

  Facility

  Therapist

  TreatmentDate

  PatientName

There are 500 Facilities.  Not sure how many Therapists.  Treatment Date is every day from January 2016 forward.  Don't know how many patients.

I have been tasked to replicate an excel report into Qlik Sense to look as close as possible to the existing report.

Anonymous
Not applicable

OK, probably thousands of Therapists and tens if not hundreds thousands of Patients.  The number of combinations is quite large.  It tells me that a table (pivot or not) is not a reasonable object here.  Unless you use a calculated condition that allows the table to calculate only if selections are made.

rittermd
Master
Master
Author

What is a reasonable object then?

If I remove the date from the pivot table it then loads in a reasonable time.  But I need the date and the other data in the visualization. 

Anonymous
Not applicable

I can't tell for sure what is a reasonable approach in this particular case, especially not knowing what is measured.  I can only recommend to try a set of charts/tables with fewer dimensions instead of one table with four dimensions.  And, keep this table with calculation condition, to see details when selections are made.
Just ask your users, do they really need an ability to see a table with many millions of cells.