topic Large Fact Table charts in QlikView

Large Fact Table charts

Tue, 21 Jan 2014 20:20:07 GMT

I have an inventory table that consists of one row per item per day in our warehouse. The number of items is approximately 97k. The time period covered in the inventory table is 3 years. This results in a fact table of about 9.3 million rows. The model I created is correct. All the tables in it are linked by a single column key. As a result, when I try and create a chart object, Qlikview wants to load all 9.3 million rows into the chart. This is not what I had in mind. I don't want the chart process until after an item selection and date range have been chosen. How do I do this?

Re: Large Fact Table charts

Clever_Anjos — Tue, 21 Jan 2014 20:23:03 GMT

If you want to postpone the graph calculation, use "Calculation Condition" into General Tab.

Something like

getselectedcount(YourDate) > 0

should do what you want

Re: Large Fact Table charts

Tue, 21 Jan 2014 20:34:31 GMT

Hi Bruce, Please create the app to make as more selections to filter out the data and then show the full details.

First, Create the one KPI to show over all managerial view and after made some selections show the full details straight table.

Re: Large Fact Table charts

Tue, 21 Jan 2014 22:08:13 GMT

Hi,

In those cases I minimize the chart while I'm doing my selections, only re-opening it when I've finished. The chart will only be recalculated when you open it again.

Maybe also you could use alternate states: making selections in one and copying them to the one the chart is based on, see this thread QlikView 11 Alternate States: copy a state to a... | QlikCommunity

Another possibility, if your chart could rely on an aggregate table made during the load with much less than 9.3 million lines, it could solve the issue.

Hope this helps.

Re: Large Fact Table charts

Tue, 21 Jan 2014 22:33:49 GMT

Hi Bruce,

my suggestion is a button and a variable.

- define a variable (for ex. "vCalc") and use the button as a toggle switch

-- action of the button is "Set Variable" and Value "=not vCalc"

-edit the "Calculation Condition" at the General Tab of all the charts to be calculated after clicking the button

-- calc condition of these is "vCalc" (no if() or so necessary)

-the initial Value of your Variable should be -1 (=true())

HtH

Roland

Re: Large Fact Table charts

stevedark — Tue, 21 Jan 2014 23:50:54 GMT

Hi Bruce,

The previous selections are totally valid, but the way I tend to approach this is to add a counter field to the main fact table (I do this as a matter of course to almost every table):

LOAD

1 as FactCount,

[... rest of table ...]

You can then use a calculation condition (on the General tab of the properties) of:

=sum(FactCount) <= 100000

This way the chart may render if you have one item picked with no date selection, or a single date selected across all items, or neither of those fields selected but still enough selections to take things to a reasonable number of rows.

If you use Calculation Conditions you should also explain to the user what you have done. Do this by clicking on the Error Messages button and then Calculation Condition Unfulfilled. This gives a box where you can type an explanation, such as Please Select Fewer Rows Of Data To View This Chart.

Also, you mention that all links are on a single key. If you can remove links from your data model - by merging data at load time - you may find performance improves. As it sounds like historical data can not change in this data set you should ensure you have an incremental load of that merged data.

Hope that helps,

Steve