concatenate allready existing rows

[stabben23]

Hi,

i have one large qvd file with all my history < today, I also have one qvd "day transaction table" >today.

I need to concatenate these two tables to get a nearly realttime data.

I concatenate and make a check Where not (Exists(KEY)).

Now to the problem: the large qvd can contain a KEY that I need to replace from the day transation table. But my Where not (Exists(KEY)) stops me.

Is there another way to do this?

[#2]

Hi,

Try using Where not Exists(Key,Key) at the load script for the second table.

Hope that helps.

-Khaled.

[stabben23]

Hi Khaled,

i dont think the Where not EXISTS is the way to solv my problem, i need another kind of check.

Key 1,2,3 need to be updated from daytransaction because it contain new Sales data.

This is a table that contain order and sales information, the order is created before the sales transaction and thats why the Key can be in both tables.

[fosuzuki · Accepted solution]

What is the order of your LOADs? If you do something like:

Data:
LOAD * FROM DayTransaction;
Concatenate
LOAD * FROM History Where Not Exists(Key);

Then it will maintain the data from the DayTransaction, which is newer.

Hope this helps you.

Fernando

[stabben23]

Hi Fernando,

After a first test this seem to work, the bad thing is that the big history qvd wont be optimized becuse of the where statment.

Thanks a lot.

//Stabben

[fosuzuki]

A simple exists() function in the where statement shouldn't affect the optimized qvd load. Maybe it's something else. Can you post the script?

[stabben23]

Hi Fernando,

this is exactly my script:

LOAD *

FROM

C:\DAYTRANS.qvd(qvd);


Concatenate (Order)

LOAD *

FROM

C:\All_Orders.qvd

(qvd) Where not (Exists(%key));

[fosuzuki]

Both qvds have the exact same fields?

[stabben23]

Exact the same, if i move the where not exists to the daytransaction, this table will losse the qvd optimized load.

All where statment is bad for qvd optimized.

[fosuzuki]

Check my sample qvw. When I load from the QVDs, the script execution progress windows says that both of them were qvd optimized loads.

To use the qvw, you must first create the QVDs...