Using "Exist()" when creating QVDs

[#1]

Hi,

I am not too technical so sorry if the below sounds too simplified.

I am creaing QVDs from DB tables, one by one, with a SQL SELECT + STORE + DROP

However I have a "size" problem.

- I have table A: "Impressions" with a 'user_key' field which I load for one month ==> QVD size is 650M

- I have a table B: "Users" which also have 'user_key' but loads all time ==> QVD size is 1.7GB

What I want to do is load table B only for 'user_key' values which are in table B.

Later when I am loading the QVDs, I use LOAD-FROM-WHERE exist().

But I want to reduce the QVD size even before.

Any idea how to do this ?

Thanks

Gur

[Gysbert_Wassenaar]

The only way to create table B so that from the start it only contains the records for users from table A is to use an sql statement that left joins table A to table B. That way the sql statement will return the set of data you want.

If that is not possible then all records from table B must be loaded into Qlikview first. You can use a left keep to immediately after loading all records throw out all that don't have a matching user in table A.

Table A

select user_key, ...etc

from mydb.tableA;

Store [Table A] into tablea.qvd;

left keep

Table B

select user_key, ...etc

from mydb.tableB;

Store [Table B] into tableb.qvd;

drop tables [Table A], [Table B];

[swuehl]

If I understood correctly, it should be enough not dropping the complete table after the SELECT - STORE of table A. Just drop all other fields except the user_key.

DROP FIELDS FieldA, FieldB;

Then do a preceding LOAD for your second table B with the where exists clause:

LOAD * WHERE EXISTS(user_key);

SELECT ... FROM tableB;

STORE tableB into ...;

Drop TABLE tableB;

Drop FIELD user_key;

[Alternatively, you can just move your first DROP table after the storing of the second table. If your two tables have the exact same fields, you need to use the NOCONCATENATE LOAD prefix on the second table load.]