topic Re: issue with EXISTS, appending data to a table in QlikView

issue with EXISTS, appending data to a table

Anonymous — Sat, 14 May 2016 13:49:38 GMT

hi Qlikkers,

I am obviously missing something simple here but I cannot figure it why this does not work. I created a script below to demonstrate the issue. I have one main table called enc which has unique key encid for that table. I also created a copy of that key as enc2.

Then I have another table called ord, which has multiple rows per encid. I need to do an append to ord table (this will be used for incremental load process).

I expect ord table populated like that if the script runs properly:

orderid,encid,ordercol1

1,1,old1

2,1,old2

3,1,old3

4,2,new4

5,2,new5

but I get this:

encid	enccol1	orderid	ordercol1
1	enc1
2	enc2	4	new4
2	enc2	5	new5

what I am missing here?

enc:
load
*,
encid as encid2
Inline [
encid,enccol1
1,enc1
2,enc2
];

//new data
ord:
load * Inline [
orderid,encid,ordercol1
4,2,new4
5,2,new5
];

// old data
CONCATENATE (ord)
load *
WHERE
not EXISTS(encid2,encid)
;
load * Inline [
orderid,encid,ordercol1
1,1,old1
2,1,old2
3,1,old3
4,2,old4
5,2,old5
]
;

Re: issue with EXISTS, appending data to a table

Gysbert_Wassenaar — Sat, 14 May 2016 14:17:42 GMT

Perhaps you're confusing encid values with orderid values. Encid values 1 and 2 exist in table enc. So no records from the last inline load will be loaded.

Re: issue with EXISTS, appending data to a table

Anonymous — Sat, 14 May 2016 14:32:29 GMT

thanks for your reply, Gysbert. what I need is to add all order rows to a final ord table if there are encid values in enc table. Enc table is a driving table that will only have encid which were added recently or modified (this will be used in incremental process).

I thought I can achieve that the code above.

Re: issue with EXISTS, appending data to a table

Gysbert_Wassenaar — Sat, 14 May 2016 14:36:22 GMT

add all order rows to a final ord table if there are encid values in enc table

Then use Exists instead of NOT Exists

Re: issue with EXISTS, appending data to a table

Anonymous — Sat, 14 May 2016 14:39:46 GMT

if I use EXISTS then it will bring old values for already loaded order rows and I only want most recent ones from the first ord load statement, so last two rows should not be loaded:

encid	enccol1	orderid	ordercol1
1	enc1	1	old1
1	enc1	2	old2
1	enc1	3	old3
2	enc2	4	new4
2	enc2	5	new5
2	enc2	4	old4
2	enc2	5	old5

Re: issue with EXISTS, appending data to a table

Gysbert_Wassenaar — Sat, 14 May 2016 14:51:25 GMT

Like I said, you're confusing encid values and orderid values. You don't want the orderid values from the old table that you have in the new table.

enc:
load * Inline [
encid,enccol1
1,enc1
2,enc2
];

//new data
ord:
load *, orderid as orderid2 Inline [
orderid,encid,ordercol1
4,2,new4
5,2,new5
];

// old data
CONCATENATE (ord)

load * Inline [
orderid,encid,ordercol1
1,1,old1
2,1,old2
3,1,old3
4,2,old4
5,2,old5
]
WHERE
not EXISTS(orderid2,orderid)
;

DROP FIELD orderid2;

Re: issue with EXISTS, appending data to a table

Anonymous — Sat, 14 May 2016 15:01:46 GMT

you rock as always, thanks!

Re: issue with EXISTS, appending data to a table

Gysbert_Wassenaar — Sat, 14 May 2016 15:04:16 GMT

You're welcome!