topic Re: Where exists () function in QlikView

Where exists () function

vongalaprashant — Wed, 25 Nov 2020 16:16:04 GMT

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

Thanks in advance:)

Re: Where exists () function

sunny_talwar — Thu, 15 Feb 2018 18:02:34 GMT

May be look at the video

Qlikview WHERE EXISTS by RFB 167 - YouTube

Re: Where exists () function

qlik4asif — Fri, 16 Feb 2018 07:48:25 GMT

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!!!!!!!!!!

Re: Where exists () function

Peter_Cammaert — Fri, 16 Feb 2018 08:30:34 GMT

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

Re: Where exists () function

vongalaprashant — Fri, 16 Feb 2018 11:59:59 GMT

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.

Re: Where exists () function

qlik4asif — Mon, 19 Feb 2018 05:05:41 GMT

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.

Re: Where exists () function

prma7799 — Mon, 19 Feb 2018 05:34:37 GMT

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!

Re: Where exists () function

vongalaprashant — Mon, 19 Feb 2018 05:40:34 GMT

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?

Re: Where exists () function

avinashelite — Mon, 19 Feb 2018 07:28:09 GMT

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

Re: Where exists () function

qlik4asif — Tue, 20 Feb 2018 04:56:27 GMT

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.

Re: Where exists () function

ksomosera10 — Tue, 20 Feb 2018 05:53:00 GMT

One Qlikview Function I used often in the load script is the exists function.

you give it a value or expression and the name of the filed and it tells you if the value is already been loaded into the field,

PRODUCT_MASTER:
Load
PRODUCT_CODE,
PRODUCT
Where Exists(PRODUCT_CODE);
SQL SELECT
PRODUCT_CODE,
PRODUCT_CODE' - 'DESCRIPTION AS PRODUCT
FROM PRODUCT_MASTER;

That pieces of code will only load product descriptions for product codes that were already loaded in previous tables. Note that the Exists function is a Qlik View function so it is used as part of the Qlik View Load statement and not as part of the SQL statement. Only one argument for the function is used in this case since the field name and the value to be checked are the same.

A second example of a place I use the Exists function is when I load temporary tables with special field names and values that fit a particular type or grouping so I can use the values to differentiate values in other fields I’m loading. Here’s an example: I load product codes into a special field that will contain only product codes for products made with 100% recycled materials:

TEMP_RECYCLE_PRODUCTS:
Load
PRODUCT_CODE as RECYCLE_PRODUCT
Resident PRODUCT_MASTER
Where CONTENT='100 POST-CONSUMER RECYCLE';

Now, I can use that field names, RECYCLE_PRODUCT, with the Exists function as I load or process other data. This technique is especially useful when the data is coming from different sources where something like a SQL join is not available. I might use it like this when loading other data:

GREEN_SALES:
//Sales of product made from recycled materials
Load
CUSTOMER,
SALES_DATE,
QUANTITY
Resident 2009_SALES_DETAIL
Where Exists(RECYCLE_PRODUCT,PRODUCT_CODE);

Drop table TEMP_RECYCLE_PRODUCTS;

At the end of the loadscript or when it is no longer needed you should drop temporary tables so that they don't unnecessarily use up memory or create unneeded associations.

In this third example, I use the Exists function to load only the first of a set of identical values as they are read in the input data. For example, this piece of code in the loadscript will only load data for the first time an error message appears in an error message log file. The input from the database is sorted into timestamp order and the Exists function in the Load statement checks to see if the specific error message value has already been loaded:

ERROR_HISTORY:
Load
ERROR_TIMESTAMP,
ERROR_MESSAGE
Where Not Exists(ERROR_MESSAGE);
SQL SELECT
ERROR_TIMESTAMP,
ERROR_MESSAGE
FROM MESSAGES_LOG
ORDER BY ERROR_TIMESTAMP;