Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

borisman
Contributor III

issue with EXISTS, appending data to a table

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
1enc1
2enc24new4
2enc25new5


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
]

;

Tags (1)
1 Solution

Accepted Solutions

Re: issue with EXISTS, appending data to a table

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;


talk is cheap, supply exceeds demand
7 Replies

Re: issue with EXISTS, appending data to a table

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.


talk is cheap, supply exceeds demand
borisman
Contributor III

Re: issue with EXISTS, appending data to a table

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

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

Then use Exists instead of NOT Exists


talk is cheap, supply exceeds demand
borisman
Contributor III

Re: issue with EXISTS, appending data to a table

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
1enc11old1
1enc12old2
1enc13old3
2enc24new4
2enc25new5
2enc24old4
2enc25old5

Re: issue with EXISTS, appending data to a table

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;


talk is cheap, supply exceeds demand
borisman
Contributor III

Re: issue with EXISTS, appending data to a table

you rock as always, thanks!

Re: issue with EXISTS, appending data to a table

You're welcome!


talk is cheap, supply exceeds demand
Community Browser