Where exists () function

[vongalaprashant]

Can Any please explain me where exists () in qlik how does it work with few clear examples?

Thanks in advance:)

[sunny_talwar]

May be look at the video

Qlikview WHERE EXISTS by RFB 167 - YouTube

[qlik4asif]

Employees:               //* Loading table1

LOAD * inline [

Employee|ID|Salary

Bill|001|20000

John|002|30000

Steve|003|35000

] (delimiter is '|');

Citizens:                    //* Loading table2

Load * inline [

Name|Address

Bill|New York

Mary|London

Steve|Chicago

Lucy|Paris

John|Miami

] (delimiter is '|');

Now i want filter data in table 2 compared to table1(common data)

EmployeeAddresses:

Load

     Name as Employee,

     Address

     Resident Citizens where Exists (Employee, Name);   //* This will look the common data in table2

  Drop Tables Employees, Citizens;  ??8 Droping both tables

Output:

Employee	Address
Bill	New York
John	Miami
Steve	Chicago

Now if the concept is clear mark it as answered!!!!!!!!!!

[Peter_Cammaert]

The QlikView On-line help contains an extensive treatment of the Exists() function (including a multi-faceted example).

See: Exists - script function - Qlikview

Expand the subtree "Examples and Results" for a series of examples with an explanation of each one.

Note that the on-line help contains treatments of all functions and statements and includes examples for each one of them. On-line help is a good starting point. See: Qlik Help

[vongalaprashant]

Hi All,

Thanks for the replies.

table1:
LOAD
    code,
    Name
FROM [lib://20153098/data.xlsx]
(ooxml, embedded labels, table is Sheet1);

table2:
LOAD
    number  ,
    "class",
     Names
FROM [lib://20153098/data1.xlsx]
(ooxml, embedded labels, table is Sheet1) where Exists(code,number);

//Rem
table3:
Load
  number as code,
    "class",
    Names

Resident table2 Where Exists(code,number) ;

I WANT TO KNOW WHY WE NEED O CREATE TABLE3 AND HOW DOES LOGIC IN TABLE3 WORKS ?

Please let me know in detail.

Thanks in Advance.

[qlik4asif]

table3:
Load
  number as code,
    "class",
    Names

Resident table2 Where Exists(code,number) ;

The values in field Code of table 1 Eg: 1,2,3,4,5,

The values in field number of table 2 Eg: ,2,,4,5,6,7,8

Since you are doing resident load with where exists on table 2 ,

it will load records having common in above fields.

result be with records having values 2,4,5

We  cal also do inner join but for making it for optimized load we can use where exists.

[prma7799]

The Exists() function only checks a single field, so that is why you need to concatenate two or more fields together into a single field if you want to compare against the combinations of multiple fields. In this case, a join would work as well.

In the book, we used Where NOT Exists() on a link table to prevent duplicate records in the link table. My thinking around this subject has changed somewhat since the publication of the book, you can find an easier solution here: DISTINCT can be deceiving » The Qlik Fix! The Qlik Fix!

[vongalaprashant]

Hi All,

Thank you for replies.

Few things could not understand here that 

1.when we want to use where exists() is it mandatory to have same field name?

2.If So after renaming  field name eg: ((number as code)),how can we compare here number with code.

As we have already changed number in to code?

[avinashelite]

if you want to completely eliminate the data then table 3 is not required at all try like this

table1:
LOAD
    code,
    Name
FROM [lib://20153098/data.xlsx]
(ooxml, embedded labels, table is Sheet1);

table2:
LOAD
    number  as code,
    "class",
     Names
FROM [lib://20153098/data1.xlsx]
(ooxml, embedded labels, table is Sheet1) where Exists(code,number);

Where Exists(code,number) ;

[qlik4asif]

1.when we want to use where exists() is it mandatory to have same field name?

It is not mandatory to use same field name, for better performance we make it as same field.

2.If So after renaming  field name eg: ((number as code)),how can we compare here number with code.

As we have already changed number in to code?

Here code is from Table 2 and number is from table 1

Both fields terminology is different , but having same number representation.