how to make given code as optimized load

[qliklearnervir]

GS:

LOAD  [BID]

Resident Book;

Left Join

LOAD

  [BID],

         if([Sequence Number]>1, 'Company', 'broker') as [ Type],

  [Address 1],

  [Address 2],

      len( [Email Adrress]) as [Email Length]     

FROM

$(vQvdFileLocation)guest.qvd

(qvd)

WHERE ([ Year] >= $(vFromYear) and [ Year] <= $(vToYear));

[Anonymous]

GS:

LOAD  [BID]

Resident Book;

TmpTable:

noconcatenate

LOAD

  [BID],

   [Address 1],

  [Address 2],

     ([Sequence Number],

       [Email Adrress],

     [Year]

FROM

$(vQvdFileLocation)guest.qvd

(qvd);

left join (GS)

LOAD

  [BID],

         if([Sequence Number]>1, 'Company', 'broker') as [ Type],

  [Address 1],

  [Address 2],

     len( [Email Adrress]) as [Email Length]     

resident TmpTable

WHERE ([ Year] >= $(vFromYear) and [ Year] <= $(vToYear));

drop table TmpTable;

[sundarakumar]

Optimized load is where you pull data from a qvd without any transformation or where clause in it.(basically data processing). Aliasing is an exceptional to this.

So if you want it to be optimized push the calculations to the place where you are creating the QVD files, should be your exctract. But to have it handles at extract or a different transform layer should be decided with the dandwidth you have between Qlikview and DB. and the DB should support the transform Queries.

This is something that needs to be tested out.

Regards,

Sunadr

[marcus_malinow]

You're correct here apart from that you are allowed a where clause when using the Exists function.

So a solution to the problem would be to load all possible year values then when joining the final table use the clause 'where exists(Year)'

[maxgro]

load from resident --> not optimized (only from qvd)

if function --> not optimized

len function --> not optimized

where clause --> not optimized

see here, steve (stevedark‌) has a good article about optimized load

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

[stevedark]

Thanks for the mention Massimo.

Steve