Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to use QV function WHERE exists for qvd optimized load

Hi everybody,

I'm not so sure if you
already know and use following trick. The first time I was disappointed with
loading performance from qvd using where condition.

Example:

load FIELD from My.qvd
(qvd)

WHERE
FIELD2='Value_for_condition';

Loading from big files took
really long and condition caused not optimized load.

I tried following:

1) Define temporary table
using load * inline [];

2) using this temp table in
condition as where exists ();

Example:

TEMP:

load * inline

[FIELD2

Value_for_or_condition

Value_for_or_condition2

];

TABLE:

load FIELD, FIELD2 from
My.qvd (qvd) WHERE EXISTS ([FIELD2]);

drop table TEMP;

*******

Well, this has certain
issues.

- Field used in a condition
has to be selected as well, even if it is not needed (otherwise not qvd optimized)

- two loads load *, x&z
as y; load * from (qvd); -> is slow, not a qvd optimized

- two condition fileds are
not possible with where exists -> FIELD2='11' AND FIELD3='22' -> causes
not optimized qvd load

How do you handle more
combined where conditions or transformation while loading from qvd file? (where
data in qvd are not in the needed format for any reason)

Regards

Stan

3 Replies
sebastiandperei
Valued Contributor

Re: How to use QV function WHERE exists for qvd optimized load

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:

Load *

From table.qvd

Where (a=100 and b=78) or (b=500 and a=69);

I guess you could do:

TEMP:

load * inline

[a, b

100, 78

69,   500

];

Left join

Load Distinct a, b, a&'_'&b as a_b Resident TEMP;

Table:

Load *

From table.qvd(qvd)

Where exists (a&'_'&b, a_b);

I dont know if this will be optimized, but you can try at less!!

Not applicable

Re: How to use QV function WHERE exists for qvd optimized load

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 -------

TABLENAME:

LOAD * , A&'-'&B AS KEY;

SELECT * FROM TABLENAME;

STORE TABLENAME into TABLENAME.qvd (qvd) ;

------- MAIN QVW --------

TEMP:

LOAD A & '-' & B AS KEY INLINE [

A , B

10, 100

32, 165

45, 90

];

LOAD * TABLENAME.qvd (qvd)

Where EXISTS(KEY);

DROP Table TEMP ;

This Load keeps Optimized load. If you are not doing any incremental load , use Autonumber functions for composite keys.

Not applicable

Re: How to use QV function WHERE exists for qvd optimized load

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.

Regards

Stan

Community Browser