update and append when there is no uniq key

[userid128223]

Hi Everyone I have data that does not have 1:1 relation ship for update and append. In order to do find 1:1 relation ship between new_qvd & Master_QVD i have to create a composite key. However when i tried doing below it still does not give me the results I am looking for. It only takes all data in New_QVD and creates new Merge_Data.

Ideally what I want is update Master Data QVD with new updates (if it exist) in New_QVD and append new records that are not there.

Merge_Data:

LOAD

     ID,

     Date,

     StoreNo,

     Name,

     amount

FROM      NEW_QVD.qvd (qvd);    

Concatenate (Merge_Data)

LOAD

     ID,

     Date,

     StoreNo

     Name,

     amount

FROM      MASTER_QVD.qvd (qvd);    

WHERE not exists(ID&StoreNo&Date);

[swuehl]

Check the two argument version of exists() function.

Try something like

Merge_Data:

LOAD

     ID,

     Date,

     StoreNo,

     Name,

     amount,

     ID&StoreNo&Date as TmpKey

FROM      NEW_QVD.qvd (qvd);   

Concatenate (Merge_Data)

LOAD

     ID,

     Date,

     StoreNo

     Name,

     amount,

     ID&StoreNo&Date as TmpKey

FROM      MASTER_QVD.qvd (qvd)

WHERE not exists(TmpKey, ID&StoreNo&Date);

drop field TmpKey;

[Anonymous]

Hi

How about combining your key fields to create new field called say UniqueKey in you load statement and saved in the qvd's.

     ID & StoreNo & Date          as IUniqueKey] ;

Then the Exists becomes simply :

     WHERE not exists(I [UniqueKey] ) ;

Best Regards,     Bill

[userid128223]

your solution did not work because field not found error so i tried it like this but still gives me error when script reaches part where it is checking for WHERE not exists(key);

Merge_Data:

LOAD

     ID,

     Date,

     StoreNo,

     Name,

     amount,

     key;

Load

     *,

     ID&StoreNo&Date as key

FROM      NEW_QVD.qvd (qvd);   

Concatenate (Merge_Data)

LOAD

     ID,

     Date,

     StoreNo,

     Name,

     amount,

     key;

FROM      MASTER_QVD.qvd (qvd);   

WHERE not exists(key);

[userid128223]

Hi Bill

I cannot change how master and new qvd's are being created not an option

[Anonymous]

dd

Just out of interest why is improving the qvd's not an option ?

Best Regards,     Bill

[userid128223]

I guess only way to do this will be to:

Load new table

Load master table

call new table via resident load and create composite key and store it again to a new table.qvd

call master table via resident load and create composite key and store it again in master table.qvd

Then do the merge table with where not exist.

I am thinking of doing above however wonder if i was using optimized coding or creating and recreating stuff.

[Anonymous]

dd

Does your qvd MASTER_QVD.qvd (contain a field called key ?

Best Regards,     Bill

[userid128223]

Sorry Bill i miss understood your question.

Yes I was thinking about doing resident load and creating composite key and then use it again to do update and merge.

[userid128223]

no master does not contain key. and problem i face is records does not have 1:1 relation ship unless you combine multiple fields.

so for each ID you could have multiple store and multiple records.

only when you select id + storeno + date you get to see uniq amount.

so to do merge and update you have to create composite key.