Need help to create a query in expression level

Tue, 04 Nov 2014 14:15:59 GMT

Hi all,

I have Six fields which are loaded by a resident table having where clause n group by.

below is my sample script for your reference.

volume:
LOAD
BUSINESS,
ULTIMATE_NAME,
LEVEL1,
LEVEL2,
DIRECT,
sum(RECEIPT_AMOUNT)/Count(DISTINCT(MONTH)) as RECEIPT_AMOUNT

Resident sometablename

where DATE > num(DATE('09/01/2013', 'MM/DD/YYYY' ))and DATE <num(DATE('08/31/2014', 'MM/DD/YYYY' ))
and FLAG='CLOSED'
Group by BUSINESS,ULTIMATE_NAME,BUSINESS_LEVEL1,BUSINESS_LEVEL2,DIRECT;

the above query is talking one hour to complete the full reload because of the group by and where conditions

Now My requirement is to decrease the reload time

with creating same query in expression level is it possible

could any one please help to get the same..

Regards,

venky.

Re: Need help to create a query in expression level

marcus_sommer — Tue, 04 Nov 2014 15:35:25 GMT

Generaly you could perform these calculations within a gui-expression. If there are enough selections or there aren't thousands of rows in table-charts the performance could be sufficient. You should simply try it.

Within the script you could use an incremental logic - Incremental Load - or create extra tables with the count(month) and so on which are linked by a keyfield or used per mapping and applymap().

But you should also have a look on your RAM (taskmanager) and your working sets properties then if qlik used the virtual RAM it would be very slow. Also to avoid a resident load and load from a qvd could improve the performance.

- Marcus

topic Re: Need help to create a query in expression level in QlikView

Need help to create a query in expression level

Re: Need help to create a query in expression level