topic remove rows of table based on a second table in QlikView

remove rows of table based on a second table

amien — Mon, 30 Apr 2012 00:44:39 GMT

i have two tables:

1\ orderID, invoiceID, itemID

2\ orderID, invoiceID

now ... i want to keep all rows in table 1 which are NOT in table 2

in other words: the rows in table 2 should be removed from table 1 (for all itemID's)

i only want to keep table 1, table 2 will be dropped.

Mon, 30 Apr 2012 02:42:53 GMT

Please try "Where not Exist".

Load table 2 first and then use Where Not Exist to load Table1.

Easy is way is to make a Key with OrderID & InvoiceID.

varunpbhandary — Mon, 30 Apr 2012 06:34:06 GMT

Hi Amien,

Another way of doing this is

tab1:

Load

orderid, invoiceid, itemid

from table1.qvw;

left join

Load

orderid, invoiceid, 1 as flag

from table2.qvw;

final:

Load

orderid, invoiceid, itemid, 'x' as junk

resident tab1 where flag<>1;

drop tab1;

Mon, 30 Apr 2012 06:37:42 GMT

Hi,

You can use exists() function here.

Table2:

load

orderID,

invoiceID,

orderID&'|'&invoiceID as check_table2

from abc.qvd;

Table1:

load

orderID,

invoiceID,

itemID

from abc.qvd where not exists( check_table2, orderID&'|'&invoiceID );

drop field check_table2;

Ashutosh