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