Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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.]