Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Viewers,
I have faced one scenario recently on optimized load.
To load 5years of data instead of loading 10years of data into qlikview without using any conditions
But without using conditions how to do this through optimized load??
Any suggestions on this?
Regards,
BoB
You need to load your year-field at first, maybe in this way:
temp:
load * inline [
year
2015
2014
2013
2012
2011
];
and then your qvd-load:
Load
F1,
F2,
F3,
F4,
,
,
,
year,
from qvd (qvd) Where exists(year);
drop table temp;
and it could be that you need to change the order of your various load-statements within the whole script then if you load other tables with a field year before the values from temp-table here will be simply added and the where might not work as expected. If this isn't possible because of various dependencies in the load-order then you could rename the original field, make your qvd-load and rename the field again.
- Marcus
This would depend on how you built your QVD. What field are you using for the comparison?
For example if you had a field with your year, you could use that for your comparison. Keeping in mind that you can only use a single where exist statement to keep your optimized load.
So create a table that contains all the years you want to load and use that as part of your exist statement .
YourTable:
Load
Field1,
Field2,
...
...
From YourQVD.qvd
Where
Exists( YearField )
;
Good luck
The only way to use a where-clause and keep the load optimized is the exists-function with one parameter like:
where exists(Field);
which meant in your case you need to load at first all dates (or maybe easier years) which you need in a field and afterwards you could use the above mentioned where-clause.
An alternatively in your case might be to split the data maybe on a year-level or in old-data and current-data and you could avoid any where-clauses on the load.
- Marcus
Still am in confuse,
In 10years of data I want to load only last 5years of data.
If I use where exists(year) it will load all the data but I want to restrict the data through optimized load.
Load
F1,
F2,
F3,
F4,
,
,
,
year,
from qvd(qvd) Where exists(year);
what are conditions we should use for optimized load?
could you explain briefly?
You need to load your year-field at first, maybe in this way:
temp:
load * inline [
year
2015
2014
2013
2012
2011
];
and then your qvd-load:
Load
F1,
F2,
F3,
F4,
,
,
,
year,
from qvd (qvd) Where exists(year);
drop table temp;
and it could be that you need to change the order of your various load-statements within the whole script then if you load other tables with a field year before the values from temp-table here will be simply added and the where might not work as expected. If this isn't possible because of various dependencies in the load-order then you could rename the original field, make your qvd-load and rename the field again.
- Marcus