topic about specification of Concatenate Load in scripts in QlikView

about specification of Concatenate Load in scripts

Fri, 27 Jul 2012 09:13:04 GMT

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,

Re: about specification of Concatenate Load in scripts

Fri, 27 Jul 2012 09:27:38 GMT

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"

Re: about specification of Concatenate Load in scripts

Wed, 01 Aug 2012 03:25:33 GMT

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.