Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

darrin_pilkingt
Contributor II

Exists question for load optimization

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Exists question for load optimization

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

7 Replies
Not applicable

Re: Exists question for load optimization

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

Re: Exists question for load optimization

check this blog post from Steve Dark

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

Maybe that could help

mov
Esteemed Contributor III

Re: Exists question for load optimization

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

darrin_pilkingt
Contributor II

Re: Exists question for load optimization

Michael,

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

Thanks

mov
Esteemed Contributor III

Re: Exists question for load optimization

I have high level of tolerance

rustyfishbones
Honored Contributor II

Re: Exists question for load optimization

Michael,

you're from New York?

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

sterligov
New Contributor II

Re: Exists question for load optimization

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

Community Browser