Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qliklearnervir
New Contributor III

how to make given code as optimized load

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));

5 Replies
hrlinder
Honored Contributor

Re: how to make given code as optimized load

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
Valued Contributor II

Re: how to make given code as optimized load

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
Valued Contributor III

Re: how to make given code as optimized load

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)'

MVP
MVP

Re: how to make given code as optimized load

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/

MVP
MVP

Re: how to make given code as optimized load

Thanks for the mention Massimo.

Steve

Community Browser