Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Something like this
Table1:
LOAD
Field,
otherfield
from Table.qvd(qvd);
LOAD
another
from table2.qvd(qvd)
where exists(another,Field);
I think you want to ignore the records. May be NOT EXISTS instead
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
LOAD
Reference
OtherItem
FROM Example1
LOAD
Reference
OtherItem
FROM Example2
where not exists(Reference)
should work
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
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
//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);