topic Re: problem in using many variable in chart in QlikView

problem in using many variable in chart

Tue, 18 Feb 2014 12:32:37 GMT

Hello every one

i have a problem and i hope you can help me!!!

ha have qvw file with 39 million record . the size of this file is about 700 mb.

because of the indication and business requirement i have to use many variable in charts expression and dim...So my file is unusable...and after any click on list box, system use 10 minutes to display the chart...

this is very important for me... please help me in this situation:)

Best Regards,

sahar

Re: problem in using many variable in chart

Tue, 18 Feb 2014 12:41:20 GMT

try to calculate possible things in backend this will remove load in your system

Re: problem in using many variable in chart

Tue, 18 Feb 2014 12:42:01 GMT

Sahar,

1) Do you all that detail ? Can you perhaps do a sum(), count() ... GROUP BY

=> it will reduce by ten, twenty the number of lines and the size of your qvw !!

You can also split the qvw into 2 applications: 1 with aggregated data (fast response time), 1 with detailed data ("bad" response time but only for very specific purposes)

2) What are the distinct values of the tables ? (Settings / Document Properties / Tables tab).

Is it OK with that or can you use a rounding to reduce that number ? Ex: timestamp (very distinctive) to date (not so distinctive)

3) Use a server with a lot of memory

Fabrice

Re: problem in using many variable in chart

gopalopsharma — Tue, 18 Feb 2014 15:22:48 GMT

Hello Sahar,

This is the problem partially with the data and also with not following best practices. We use 6-7 GB CSV files but report works fine.

In addition to above points..

1. Use QVD files

2. Load only the fields which are desired

3. shift entire calculation (to the extent possible) to qvd generator

4. Check the data model, avoid any Snow flake, Synthetic key or circular loop

5. Qlikview calculates only for the objects which appear in the view, so those objects should have simple dimensions and expressions. Try to avoid use of "IF" condition to the extent possible.

6. Remove any conditional color formatting you are using in any chart, use default color or create color mapping if required

7. Load date field as: num(Date) as Date

8. Avoid variables to the extent possible, avoid triggers esp Document Open triggers

Re: problem in using many variable in chart

Wed, 19 Feb 2014 05:30:53 GMT

HI All

tanks for you reply ....

in my situation because of some parameter that should user choose it in dashboard and see the result, i have to use more variable in charts expression... i really dont know how can i avoid this...

sahar

Re: problem in using many variable in chart

gopalopsharma — Wed, 19 Feb 2014 11:03:25 GMT

I am assuming you have input box/ Slider object where user can select the value of a variable and then that value is consumed in the chart expression.

If possible try to create new fields in the data while loading, based on these conditions and then in chart use conditional expressions to show Aggregation of those fields.

One more thing to take care, avoid comparison of Text values, instead assign numeric values (ID) to them and then use ID in comparison. e.g. Avoid Country= "ABC" and use CountryID=1

One advice, probably you are already aware, while don't make changes in he large QV file you have instead load a sample data by using "First 100000" before Load statement, and then use this sample file for modifications. It reduces the pain to debug the changes.

Re: problem in using many variable in chart

Sat, 22 Feb 2014 07:42:23 GMT

thanks for your useful advices. as you told i have some input box and for each of them i have variable .. when user type some number in those input boxes the charts must load the expression and dimension base on variable...

how can i use field in data model and conditional expression ?

can i pass field from input box to script and load dashboard?

Regards,

sahar

Re: problem in using many variable in chart

Sun, 23 Feb 2014 06:24:17 GMT

how can i use field in data model and conditional expression ?

can i pass field from input box to script and load dashboard?

Regards,

sahar

Re: problem in using many variable in chart

tresB — Sun, 23 Feb 2014 06:33:59 GMT

May be this discusion : option for user input when reloading script

could help you.

Re: problem in using many variable in chart

marchoctober — Sun, 23 Feb 2014 07:03:18 GMT

Please share in here:

1) your current data model screenshot;

2) your chart dimentions, expressions and variables;

3) The task you want perform in this chart;

4) The server configuration you are running this QVW.

And we will provide you with appropriate advise or solution.

In other case please see above the recomendation in data modelling.