topic Re: concatenate allready existing rows in QlikView

concatenate allready existing rows

stabben23 — Fri, 11 Jan 2013 09:26:39 GMT

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?

Re: concatenate allready existing rows

Fri, 11 Jan 2013 09:44:50 GMT

Hi,

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

Hope that helps.

-Khaled.

Re: concatenate allready existing rows

stabben23 — Fri, 11 Jan 2013 10:06:17 GMT

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.

Re: concatenate allready existing rows

fosuzuki — Fri, 11 Jan 2013 10:27:55 GMT

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

Re: concatenate allready existing rows

stabben23 — Fri, 11 Jan 2013 11:15:59 GMT

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

Re: concatenate allready existing rows

fosuzuki — Fri, 11 Jan 2013 11:40:05 GMT

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?

Re: concatenate allready existing rows

stabben23 — Fri, 11 Jan 2013 12:07:38 GMT

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));

Re: concatenate allready existing rows

fosuzuki — Fri, 11 Jan 2013 12:15:23 GMT

Both qvds have the exact same fields?

Re: concatenate allready existing rows

stabben23 — Fri, 11 Jan 2013 12:20:57 GMT

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.

Re: concatenate allready existing rows

fosuzuki — Fri, 11 Jan 2013 12:35:31 GMT

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...