Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

remove rows of table based on a second table

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.

3 Replies
Not applicable

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
Partner - Contributor III
Partner - Contributor III

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;

Not applicable

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