topic what's wrong with this expression in QlikView

what's wrong with this expression

ali_hijazi — Wed, 30 May 2012 07:33:04 GMT

Hello I got a QVW document that has a fact table with 22 columns and 121,000,000 records

The size of the document on the disk is 2.7GB; and when I open the document it consumes about 8.5 GB of RAM

in this document I got only one pivot table with the following expression;

whenever I open the pivot table (initially collapsed), the document inflates and consumes aobut 19GB of RAM

is this normal? Actually I'm unable to continue with the development as the document stalls several times

even if i filter the document the calculation takes a lot of time; I need to wait about 1 minute for the expression to return results

the expression is as follows and is used to calculate the effective retail:

sum

(

Aggr

(

FirstSortedValue

(

{

[Transaction Date]={"<=$(=vSelectedDate)"}

}

[Amount USD w/o VAT],-[Transaction Date]

)

sum

(

TOTAL

<[Geo Country],Brand,[BU Code],RetailKey>

{

[Transaction Date]={"<=$(=vSelectedDate)"}

} Quantity

)

,[Geo Country],Brand,[BU Code], RetailKey

)

what's wrong with this expression

alvinford — Wed, 30 May 2012 07:41:52 GMT

Hi Ali , There is , in between this statement pls check [Amount USD w/o VAT],-[Transaction Date].

Regards,

Alvin.

what's wrong with this expression

alvinford — Wed, 30 May 2012 07:45:38 GMT

Sry there are also syntax errors in the expression where you are using the set analysis . Please check .

Regards,

Alvin.

what's wrong with this expression

ali_hijazi — Wed, 30 May 2012 07:49:36 GMT

I fixed the error in the post above

however this is not the problem the expression is giving correct results

yet the response is extremely slow

when the chart is collapsed the document only consumes 8 GB of RAM

when I expand the chart the document consumes almost 20GB why?

can we optimise the expression?

thank you

what's wrong with this expression

alvinford — Wed, 30 May 2012 08:04:10 GMT

Hi Ali, Pls try to left join the dimensions with the main table .

There will be increase in the Reload time .. But hope fully the Ram Consumption will be less. And also try to group it at the Script level.

Lets give a try ....

Regards,

Alvin.

Re: what's wrong with this expression

jonathandienst — Wed, 30 May 2012 09:06:09 GMT

Do all the fields in the expression come from the main table? Or are they closely associated (one jump to the main table only). If not, then QV is having to do a lot of work to perform the filtering, leading to poor performance and memory bloat.

Bringing all the fields into the main table (or no more than 1 jump away) may improve performance.

Regards

Jonathan

what's wrong with this expression

ali_hijazi — Wed, 30 May 2012 10:16:59 GMT

ok I'll try to make all the columns be in the fact table using joins

but is there any difference whether these columns are key columns or not?

Please advise