topic Re: Exists question for load optimization in QlikView

Exists question for load optimization

Anonymous — Tue, 15 Oct 2013 19:37:46 GMT

Since most where statements other than Exists do not keep a load optimized I have created a temp table storing the dates I wish to pull using the Exists(DateKey) to keep my load optimized but I have ran into a problem that makes what I think is an ugly solution when I have multiple tables to load with different Date names.

We already have a calendar table and the where allows me to only grab the dates I want. I have named the date according to the Payments table I will later load.

tDate:

LOAD

DateKey as PostedDt

FROM

CalendarTraditional.qvd (qvd)

where DateKey >= YearStart(today(), -1) and DateKey <= MonthEnd(today());

Payments:

LOAD PaymentID,

PostedDt,

PaymentCounter

FROM Payments.qvd (qvd)

where Exists(PostedDt);

This works great and is optimized. The problem is I am then going to add a second table where the Date field is name CreateDt and this load will not be optimized because Exists(PostedDt, CreateDt) seems to unOptimize it.

Journals:

Load JournalID,

CreateDt

JournalCounter

From Journals.qvd (qvd)

where Exists(PostedDt, CreateDt);

The only solution I could think of was to do a Resident load of my tDate table and rename the Date to suit each table I am about to load. It would be fast but kind of ugly in my opinion.

Any thoughts?

Thanks

Re: Exists question for load optimization

Tue, 15 Oct 2013 19:49:02 GMT

Unfortunately, I do not think there is another solution.

I recently had the same issue and i renamed the field because the optimized load does not work when the name of the search field is different from the field name in the table loaded.

Re: Exists question for load optimization

rustyfishbones — Tue, 15 Oct 2013 19:55:50 GMT

check this blog post from Steve Dark

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

Maybe that could help

Re: Exists question for load optimization

Anonymous — Tue, 15 Oct 2013 20:27:25 GMT

You mean this, I guess:

tDate:

LOAD

DateKey as PostedDt ,

DateKey as CreateDt ,

DateKey as <another date>

FROM CalendarTraditional.qvd (qvd)

where DateKey >= YearStart(today(), -1) and DateKey <= MonthEnd(today());

There is nothing ugly about that, not for me at least... Anyway, it is just a temp table.

Regards,

Michael

Re: Exists question for load optimization

Anonymous — Tue, 15 Oct 2013 20:30:15 GMT

Michael,

Don't you hate when something so simple is staring you in the face.

Thanks

Re: Exists question for load optimization

Anonymous — Tue, 15 Oct 2013 20:33:27 GMT

I have high level of tolerance

Re: Exists question for load optimization

rustyfishbones — Tue, 15 Oct 2013 20:48:00 GMT

Michael,

you're from New York?

You're made of stern stuff over there as well as a high tolerance

Re: Exists question for load optimization

Anonymous — Wed, 16 Oct 2013 18:36:59 GMT

Thanks you. No, I'm from Ukraine. It recently joined the community, I work at a large bank: www.sberbank.ua

Отправлено с iPad

15 окт. 2013, в 23:48, Alan Farrell <qcwebmaster@qlik.com> написал(а):

QlikCommunity
Exists question for load optimization
reply from Alan Farrell in Scripting - View the full discussion
Michael,

you're from New York?

You're made of stern stuff over there as well as a high tolerance
Reply to this message by replying to this email, or go to the message on QlikCommunity
Start a new discussion in Scripting by email or at QlikCommunity
Following Exists question for load optimization in these streams: Inbox
© 1993-2013 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA