Exists question for load optimization

[Anonymous]

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

[#2]

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.

[rustyfishbones]

check this blog post from Steve Dark

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

Maybe that could help

[Anonymous · Accepted solution]

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

[Anonymous]

Michael,

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

Thanks

[Anonymous]

I have high level of tolerance

[rustyfishbones]

Michael,

you're from New York?

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

[Anonymous]

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