Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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));
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;
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
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)'
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/
Thanks for the mention Massimo.
Steve