topic Ignore if Exists in other Table in QlikView

Ignore if Exists in other Table

Thu, 14 May 2015 14:40:01 GMT

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

Re: Ignore if Exists in other Table

Miguel_Angel_Baeyens — Thu, 14 May 2015 15:17:22 GMT

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‌

Re: Ignore if Exists in other Table

Clever_Anjos — Thu, 14 May 2015 15:18:18 GMT

Something like this

Table1:

LOAD

Field,

otherfield

from Table.qvd(qvd);

LOAD

another

from table2.qvd(qvd)

where exists(another,Field);

Re: Ignore if Exists in other Table

Thu, 14 May 2015 15:20:23 GMT

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

Re: Ignore if Exists in other Table

Thu, 14 May 2015 15:26:55 GMT

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

Re: Ignore if Exists in other Table

Clever_Anjos — Thu, 14 May 2015 15:30:38 GMT

LOAD

Reference

OtherItem

FROM Example1

LOAD

Reference

OtherItem

FROM Example2

where not exists(Reference)

should work

Re: Ignore if Exists in other Table

Thu, 14 May 2015 15:42:06 GMT

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

Re: Ignore if Exists in other Table

Miguel_Angel_Baeyens — Thu, 14 May 2015 15:43:55 GMT

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

Re: Ignore if Exists in other Table

anbu1984 — Thu, 14 May 2015 15:52:01 GMT

//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);