building an efficient app

[#1]

Hi

I thought it would be usefull to hear tips and advice as to how to make your app efficient starting with the design,

eg: if you want to group a field into predetermined buckets then its best done in the load script.

Does anyone else have tips like this that will help with efficiency?

[#2]

Hi,

What does it mean group of field into predetermined bucket?

On my side, i always do 3 tiers STAR. Pure load from source, business logic and star shcema, the dashboard.

but i think (not very sure, just saying) star has a weakness, just one, the link table tend to grow tremendously.

but, you can reuse the QVD, which is great.

[#3]

This is some advice i have come accross already from other Qlikcommunity users

• When you are developing, you should avoid the if's as much as possible, using 'set analysis', this would improve the speed of your application.

From John Witherspoon

Some random ideas:

• Only load fields you need. Never load *.
• Load optimized from QVDs. Use a single exists() on these loads if practical, with the most restrictive field. Restrict further using inner joins after the fact instead of putting conditions on the main load from the QVD.
• Minimize the number of loads and joins.
• When you need to add conditional logic to a chart, data model changes are faster than set analysis, and set analysis is faster than if().
• Adding conditions to expressions seems to be faster than adding conditions to dimensions.
• When doing set analysis, using a pre-generated flag with values of 1 and null seems to be faster than searching for or listing values.
• Don't use date islands on large data sets. There are usually faster solutions, even if they may be more complicated.
• When you need the distinct values of a field from a large table, for instance all of the dates, you can avoid loading from the table and get the values directly if you do it like this:

LOAD date(fieldvalue('MyDate',iterno())) as MyDate
AUTOGENERATE 1
WHILE len(fieldvalue('MyDate',iterno()));

• Avoid macros if possible.
• You absolutely MUST have enough RAM to load and process the application. In memory data models don't want to be swapped to disk.
• Be careful not to duplicate rows during joins. In some cases, the application will still function properly, but more slowly. Of course in other cases it won't function properly at all, but that's more obvious, and not a performance concern specifically.
• I have no hard numbers, but it often seems like denormalizing small tables onto big tables helps performance. So I wouldn't, say, have a product table with nothing but a product code and a product name on it. Go ahead and join the product name onto, say, the customer orders table, even if that means it's on a million rows instead of on a hundred rows. QlikView's compression will take care of it. The script will go a little slower, but you'll probably save time in the charts.

[#4]

Hi,

sorry i should have explained further, I've been told if you have a field such as age and you want to show age in the following 'bukets/groups: 0-15,16-30,30+

Then you should create an extra column in your load script which will state which group the age belongs to rather than using an expression.

[#5]

Wow, those points are really good.

For bucketing yes, should create an extra column.

I have tried to do it directly in dimension, there are not very flexible.

What about the schema? do you always build your app into star schema?

how do u manage it when your data volumn grow?

I did all my renaming during the middle tiers, together with the data volumn, i'm thinking if it would be faster to rename at the first tier. so the middle their can just do optimise load.

[Anonymous · Accepted solution]

I think you will find useful tips in the governance material posted by John.

http://community.qlik.com/docs/DOC-2614

I'd recommend you go through the qlikview best practices document.

Regards

MultiView

[#7]

I am trying to find an alterantive to a nested if statement.

I have 5 variables and if any of the 5 are true I want to set an expression to true.

is an IF OR statement better?

Or is there any other alternative.

[rich_mcevoy]

For your 5 variables try:

IF((<Condition1> + <Condition2> + <Condition3> + <Condition4> + <Condition5>) < 0,TRUE, FALSE)

TRUE() equals -1 in Qlikview, therefore if all conditions are True, your total would be -5.  Since you only require 1 truth, this formula should work...and no nested IFs.