Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
]
;
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;
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.
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.
add all order rows to a final ord table if there are encid values in enc table
Then use Exists instead of NOT Exists
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 |
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;
you rock as always, thanks!
You're welcome!