topic Updating a record in QlikView

Updating a record

Wed, 14 Dec 2011 13:25:12 GMT

Hello everyone,

I'm trying to update a qvd file with qvd file.

The code that I'm using is

Invoices:
LOAD %Invoice_KEY ,
// %Invoice_KEY ,
     InvoiceCode,
     CustomerID,
     CustomerName,
     Description,
     InvoiceDate,
     InvoiceTotal
FROM QVD\Invoices.qvd(qvd);

Concatenate
LOAD %Invoice_KEY ,
     InvoiceCode,
     CustomerID,
     CustomerName,
     Description,
     InvoiceDate,
     InvoiceTotal
FROM
QVD\Invoices_Payments_updates.qvd
(qvd)
Where Exists(%Invoice_KEY);

But I get two records instead of the second one...

Can someone, please, help me?

Thanks in advance for the attention.

Kind regards

Piskitta

Updating a record

Wed, 14 Dec 2011 13:45:49 GMT

Hallo piskitta,

If I look at your code it should be correct that you get two records.

You have a Where statement that says that you want to concatenate the rows on a keyfield that already exists.

You do not delete the old one!

Kind regards

Gert

Updating a record

Wed, 14 Dec 2011 13:53:12 GMT

Hi Gert,

Thank you for answering in such a short time.

I realise what you're saying. I'm really in new in Qlikview enviornment, so it's still really difficult to see those mistakes...

Can you tell me how to delete the old record?

Thanks.

Kind regards

Updating a record

Wed, 14 Dec 2011 13:59:48 GMT

Hallo piskitta,

For an update you will need to have a modification time. Meaning with the first QVD load you check the modification time like: WHERE ModificationTime >=#$(LastExecTime)#;

Then you perform a Concatenate with the new data with a where NOT clause like:WHERE NOT Exists(PrimaryKey);

Hope this helps!

Gert

Updating a record

Wed, 14 Dec 2011 14:06:09 GMT

Thank you Gert, once again.

Kind regards

Piskitta

Updating a record

rwunderlich — Wed, 14 Dec 2011 15:23:05 GMT

Your condtion should be

Where NOT Exists(%Invoice_Key)

-Rob

Updating a record

Wed, 14 Dec 2011 15:26:42 GMT

Rob's answer is faster, but using Rob's answer it is important in what sequence you will load the QVD's

Updating a record

Wed, 14 Dec 2011 16:03:33 GMT

Hello Rob and Gert

I think I'm not being correct in my words... Sorry...

My intention is update only two columns of the same line.

I tested the solutions that are being presented but they return only the new records.

I don't change my primary keys because it's not a new record that i'm trying to insert. I'm trying to update the information of the column with a defined primary key...

Is this even possible?!

Thanks for your help.

Kind regards

Piskitta

Updating a record

rwunderlich — Wed, 14 Dec 2011 19:37:22 GMT

You should load the updates.qvd first.

-Rob

Updating a record

Thu, 15 Dec 2011 08:16:40 GMT

Thanks Rob.

Sometimes we justr can't see the trees in the florest...

Kind regards

Piskitta