topic how to make given code as optimized load in QlikView

how to make given code as optimized load

qliklearnervir — Thu, 20 Aug 2015 11:34:16 GMT

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

Re: how to make given code as optimized load

Anonymous — Thu, 20 Aug 2015 11:40:33 GMT

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;

Re: how to make given code as optimized load

sundarakumar — Thu, 20 Aug 2015 12:39:16 GMT

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

Re: how to make given code as optimized load

marcus_malinow — Thu, 20 Aug 2015 12:48:28 GMT

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

Re: how to make given code as optimized load

maxgro — Thu, 20 Aug 2015 12:56:32 GMT

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/

Re: how to make given code as optimized load

stevedark — Thu, 20 Aug 2015 13:31:39 GMT

Thanks for the mention Massimo.

Steve