Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
check this blog post from Steve Dark
http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/
Maybe that could help
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
Michael,
Don't you hate when something so simple is staring you in the face.
Thanks
I have high level of tolerance
Michael,
you're from New York?
You're made of stern stuff over there as well as a high tolerance
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