Discussion Board for collaboration on QlikView Scripting.
I'm not so sure if youalready know and use following trick. The first time I was disappointed withloading performance from qvd using where condition.
load FIELD from My.qvd(qvd)
Loading from big files tookreally long and condition caused not optimized load.
I tried following:
1) Define temporary tableusing load * inline ;
2) using this temp table incondition as where exists ();
load * inline
load FIELD, FIELD2 fromMy.qvd (qvd) WHERE EXISTS ([FIELD2]);
drop table TEMP;
Well, this has certainissues.
- Field used in a conditionhas to be selected as well, even if it is not needed (otherwise not qvd optimized)
- two loads load *, x&zas y; load * from (qvd); -> is slow, not a qvd optimized
- two condition fileds arenot possible with where exists -> FIELD2='11' AND FIELD3='22' -> causesnot optimized qvd load
How do you handle morecombined where conditions or transformation while loading from qvd file? (wheredata in qvd are not in the needed format for any reason)
Thanks for the idea! I didn't know this way to do an optimized load.
So, if I understood, you are asking how you could do a combination of conditions. I think you could do something like:
suposing that we will loading like:
Where (a=100 and b=78) or (b=500 and a=69);
I guess you could do:
load * inline
Load Distinct a, b, a&'_'&b as a_b Resident TEMP;
Where exists (a&'_'&b, a_b);
I dont know if this will be optimized, but you can try at less!!
Hi , If you concatenate in the where exists function , it changed qvd load as un-optimized load. In this case in the QVD loader we need to create the composite keys.
-------- QVD Loader -------
LOAD * , A&'-'&B AS KEY;
SELECT * FROM TABLENAME;
STORE TABLENAME into TABLENAME.qvd (qvd) ;
------- MAIN QVW --------
LOAD A & '-' & B AS KEY INLINE [
A , B
LOAD * TABLENAME.qvd (qvd)
DROP Table TEMP ;
This Load keeps Optimized load. If you are not doing any incremental load , use Autonumber functions for composite keys.
Thank you for the reply.
I was thinking about the same composite key approach. However you can never know if there won't be a similar need with another concatenated key. Then there has to be another A&C field.
In a selection, all possible combinations should be given, not just where Field (A,B,C) AND Field2(D,E,F). You should know then combinations like AD, AE, AF, BD, BE, BF ....
But I will keep it on my mind.