where not exits- i'm kind of lost

[raz-datamind]

hi all,

i have a problem with where not exist- i either get too much data, or too few data.

i have a source table and a qvd.

load key, field1, field2,... from csv;

load key, field1, field2,... from qvd;

i need to make sure that if the key exists in the csv not to load the relevant data from the  qvd, as csv data for that key is newer and overrides key's data from the qvd.

i do i do it?

i looked on examples in the forum and qv help, and its not working right- i either get just one unique record for each of the keys from qvd which are not in the csv, or i get multiple data.

thanks,

Raz.

[Anonymous]

To make sure you're dealing with the right key I would create a copy to test against:

load key, key AS Testkey, field1, field2,... from csv;

load key, field1, field2,... from qvd

where not exists (Testkey, key);

This will make sure that qvd is only loaded when not in qvd. It won't guard against more than one record from the qvd being loaded if the key is not unique.

Jonathan

[swuehl · Accepted solution]

So you can have multiple occurrences of the same key value in your qvd?

exists() determines whether a specific field value exists in a specified field of the data loaded so far, even in the same load statement execution (e.g. in the qvd load).

So maybe try something like:

load key, key as csvkey, field1, field2,... from csv;

concatenate load key, field1, field2,... from qvd where not exists(csvkey, key);

drop field csvkey;

Hope this helps,

Stefan

[raz-datamind]

Key is not unique, that ok.

Thanks, that works great.