Pivot chart causes QVW to hang

[#1]

Hello,

I am designing an application in QV 9.0 SR5. It has many sheets, triggers, etc... and so it pretty complex. it's all working just fine except for one pivot chart. When loaded with data, the file is about 16MB. the load script runs just fine and all of my data is in the QVW as I'd expect it to be. The chart in question is minimized by default and when the user clicks on it, a trigger restores it and then the QVW essentially hangs - stops responding. I have waited for calculations to complete up to 5 minutes but the file does not start working again.I ave to end the QV task in Windows and start over.

I did a test where I limited the number of rows coming into the QVW via the load script by about half and in that test, the chart worked as expected. This makes me think the problem is with the number of records being processed for this chart. There are ~ 9,100 records in the table used for this chart.

I did another test where I let all of the data be loaded, but simplified the one expression used inside the chart. I see the same hanging problem then. This is the expression I have right now:

[CODE]

=if (date([DateCurEst],'DD') > 0,
date([DateCurEst],'DD') & chr(10) & chr(10) &
[Project Name] & ' (' & date([DateCurEst],'DD MMM YYYY') & ')' & chr(10) &
'Score: ' & [Score] & chr(10) &
'Stage: ' & [Stage Name]) & chr(10) &
'Status: ' & [Decision] & chr(10) &
'Start Date: ' & [Start Date] & chr(10) &
'End Date: ' & [End Date] & chr(10) &
'Project ID: ' & ProjectID

[/CODE]

This QVW workks fine when loaded from other customer's data sources so I don't think the problem lies in the chart's design or the load script, specifically. I think this is something about the combination of this customer's data, the QVW's design and the load scripts when combined.

I'm unsure where to go next as far as troubleshooting this issue. I cannot upload the QVW because it contains a lot of customer data, both in the configuration and the loaded data. I cannot reproduce this problem in any other QVW so I'm at a loss.

What kinds of general troubleshooting tips do you all have for cases like there, where the QVW just stops responding on one particular chart?

Thanks for any ideas,

[chris_johnson]

Hi Peter,

Do you think it would make any difference if you moved the formula from the table into the script? I'm still learning my way with QlikView myself but sometimes I find it helps speed things up a little.

Hopefully someone with a little more 'know-how' can point you in the right direction!

Regards,

Chris

[#3]

Let me preface this by saying that I am absolutely not an expert on this so take what I say with a grain of salt. Anyway, I don't really see anything in that expression that would make the chart too big to calculate. I've had charts with an aggr nested inside of an aggr with if statements running conditions on 20 million + records and while they calculate pretty slow (5 or so minutes), they still calculate. However, I do that on a qvw that was made by people with titles like "data architect" that really know what they are doing. Sometimes when I make my own qvw and I screw the data model up pretty bad stuff like what you are describing happens. Yeah that was pretty vague... but maybe the somewhat unrelated example below will help.

For example, let's say that I have a table with invoices and the date field is [Invoice Date] but I have another table with a date field just called [Date] (and the two tables are not associated in any way). If I have an expression like =sum([Invoice Balance]) and I accidentally use [Date] as the dimension instead of [Invoice Date] then the chart would hang there pretty much forever and never calculate which sounds like what is happening to you (this actually happened to me literally 5 minutes ago).

However, if you've used[DateCurEst], [Project Name], [Score],[Stage Name],[Decision],[Start Date],[End Date], and [Project ID] in the same expression before and it's been fine then I have no idea what's going on. However, I would look really closely to make sure they are all associated in some way.

[#4]

Thanks for that advice, Trent. I think you may have helped figure this out. I have to test next, with others' help here. I'll post back here on my results.

[Oleg_Troyansky]

To add to Trent's comments:

Pivot Tables are the heaviest objects in QlikView, generally. With that, the most common reasons for slow performance are:

1. IF() formulas inside the aggregation functions. Your expression didn't look this way, but maybe you have any other expressions?

2. Calculated Dimensions, particularly with IF() formulas

3. Accidental "many-to-many" relationship or a Cartesian join - an example that Trent explained

4. Fields coming from multiple tables and too many "links" that need to be resolved in the run-time. Even if the data is linked correctly and there is no Cartesian join, if components from your expressions are coming from multiple tables, and there are multiple join links that needs to be resolved, it may still affect performance.

hope it helps,