Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Ignore if Exists in other Table

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

1 Solution

Accepted Solutions

Re: Ignore if Exists in other Table

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

8 Replies

Re: Ignore if Exists in other Table

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

Employee
Employee

Re: Ignore if Exists in other Table

Something like this

Table1:

LOAD

      Field,

      otherfield

from Table.qvd(qvd);

LOAD

     another

from table2.qvd(qvd)

where exists(another,Field);

Not applicable

Re: Ignore if Exists in other Table

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

Not applicable

Re: Ignore if Exists in other Table

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

Employee
Employee

Re: Ignore if Exists in other Table

LOAD

     Reference

     OtherItem

FROM Example1

LOAD

     Reference

     OtherItem

FROM Example2

where not exists(Reference)

should work

Not applicable

Re: Ignore if Exists in other Table

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

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

Re: Ignore if Exists in other Table

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