about specification of Concatenate Load in scripts

[#1]

hi,

please let me know that about subtitle.

i'm wondering result to reload the following scripts.

Item:

LOAD

Item_cd & '-' & Item_location as Primary Key,

Item_qty;

SQL

select * from DB;

CONCATINATE(Item) LOAD

Item_cd & '-' & Item_location as Primary Key,

Item_qty

from file.qvd;

which is this result update or insert as new data from qvd if both Primary Key are same?

if result is insert then how can i update(delete & insert) to Item teble using Primary Key?

please anyone tell me this matter...

Regards,

[#2 · Accepted solution]

Lets say you have 2 tables:

TableSource:

LOAD * INLINE [

TableSource.PK, TableSource.Value

1, 1

2, 2

3, 4

4, 8

5, 16

];

TableUpdate:

LOAD * INLINE [

TableUpdate.PK, TableUpdate.Value

4, UPDATED

5, UPDATED

6, 32

7, 64

];

If you load with a CONCATENATE, you will have inserted rows:

Table:

LOAD

TableSource.PK AS PK,

TableSource.Value AS Value

RESIDENT TableSource;

CONCATENATE (Table)

LOAD

TableUpdate.PK AS PK,

TableUpdate.Value AS Value

RESIDENT TableUpdate;

If you want that your table "TableUpdate" really update your data, I advise you to think upside down: you first load the "update" table, then you load the first table, but filtering on the PK that don't exist.

Table:

LOAD

TableUpdate.PK AS PK,

TableUpdate.Value AS Value

RESIDENT TableUpdate;

CONCATENATE (Table)

LOAD

TableSource.PK AS PK,

TableSource.Value AS Value

RESIDENT TableSource

WHERE NOT EXISTS(PK, TableSource.PK);

If you have more than 2 sources, you will have to be smarter:

- load data in a temp table, with a "date" field

- left join the the last date in a "last_date" field with a "max()" function group by PK

- load your data in a new table, filtering on "date = last_date"

[#3]

Hi Nicolas

Thank you for your help!

I understand this specification..and solved it by using your method.

I'm really helped your answer.

Regards.