Ignore if Exists in other Table

[#1]

Good afternoon,

I am loading two tables and I am trying to ignore rows from one if a certain reference value exists in the other.

How can this be achieved?

Thanks in advance,

Rob

[Miguel_Angel_Baeyens]

Rob,

Use EXISTS like in

Customers:
LOAD ID,
     Address
FROM Customers.qvd (qvd);

Orders:
LOAD ID AS CustomerID,
     OrderNo,
     Amount,
     Date
FROM Orders.qvd (qvd)
WHERE NOT EXISTS(ID, CustomerID);

Miguel

EDIT thanks to imadelhoussein‌

[Clever_Anjos]

Something like this

Table1:

LOAD

      Field,

      otherfield

from Table.qvd(qvd);

LOAD

     another

from table2.qvd(qvd)

where exists(another,Field);

[#4]

I think you want to ignore the records. May be NOT EXISTS instead

[#5]

The tables have the same structure (example below) so will combine into one table after load, but I need to remove the records from Example2 where the reference exists in Example1... is this doable?

LOAD

     Reference

     OtherItem

FROM Example1

LOAD

     Reference

     OtherItem

FROM Example2

[Clever_Anjos]

LOAD

     Reference

     OtherItem

FROM Example1

LOAD

     Reference

     OtherItem

FROM Example2

where not exists(Reference)

should work

[#7]

Thanks for this - what if you wanted to do this based on a formulated field (NewRef) as per below)?

LOAD

     Reference,

     OtherItem,

     Left(Reference,3) as NewRef

FROM Example1

LOAD

     Reference,

     OtherItem,

     Left(Reference,3) as NewRef

FROM Example2

[Miguel_Angel_Baeyens · Accepted solution]

Yes, use just one field instead of two as parameter for the EXISTS()

Table1:
LOAD ID,
     Left(ID, 3) AS ShortID,
     CustomerName
FROM Source;

Table2:
LOAD ID,
     Left(ID, 3) AS ShortID,
     CustomerName
FROM Source2
WHERE NOT EXISTS(ShortID, Left(ID, 3));

Miguel

[anbu1984]

//Removes duplicates based on Reference field present in T2

T1:
Load * Inline [
Reference,OtherItem
1,aa
2,bb ];

T2:
Load * Inline [
Reference,OtherItem
2,bb
3,cc
3,dd ] Where Not Exists(Reference);

//Duplicates present in T2 is retained

T1:
Load Reference As Ref,OtherItem Inline [
Reference,OtherItem
1,aa
2,bb ];

T2:
Load Reference As Ref,OtherItem;
Load * Inline [
Reference,OtherItem
2,bb
3,cc
3,dd ] Where Not Exists(Ref,Reference);