topic Very basic Where Exists question in QlikView

Very basic Where Exists question

kschramm — Fri, 04 Dec 2015 20:47:08 GMT

New to QlikView and trying to understand the Where Exists syntax. All I am trying to do is only load data from one table for postal codes that are in the Zips table. I am not clear on what the two parameters are in the where exists statement and the doc is not very specific. I would have thought that one of them was the column that you want to check and one would be the table that you are checking against but really need an explanation. Thank you in advance for anyone's assistance.

Zips:

Load * inline [

94519,

91950,

91910,

94704,

94010,

90706,

90210,

91502,

94014,

92118,

91945,

90241,

];

GeoProperty:

LOAD CustomerID as GeoCustomerID

resident Customer;

left join

LOAD CustomerID as GeoCustomerID,

FirstValue(BillToAddressID) as GeoBillToAddressID

resident OrderHeader

group by CustomerID;

Left Join

Load

BillToAddressID as GeoBillToAddressID,

AddressLine1 as GeoAddressLine1,

AddressLine2 as GeoAddressLine2,

City as GeoCity,

[State / Province] as [Geo State / Province],

LEFT([PostalCode],5) as [GeoPostalCode]

resident BillToAddress

Where Exists (Zips,PostalCode); <---------------------

Re: Very basic Where Exists question

sunny_talwar — Fri, 04 Dec 2015 20:57:33 GMT

Try this:

Where Exists (Zips, Left(PostalCode, 5));

Re: Very basic Where Exists question

kschramm — Fri, 04 Dec 2015 21:02:40 GMT

Thank you. I will try that.

So basically the first argument represents the table you want to check against and the second argument is the column you are comparing against the data in argument 1?

Re: Very basic Where Exists question

Fri, 04 Dec 2015 21:07:17 GMT

It does not check within a table but within another dimension.

Your inline table is missing a column name. Once you have it, use it, not the table name.

Re: Very basic Where Exists question

swuehl — Fri, 04 Dec 2015 21:07:27 GMT

The first argument to Exists() function need to be a field name. 'Zips' is a table name, and the table misses an embedded field name, so the field is called '94519'.

The second argument to Exists() is an expression, that is evaluated from the input table records as they are coming in.

If the evaluated value is found in the field '94519' loaded in your script so far, EXISTS() returns true and the WHERE clause will let the input record pass.

Since there is no field 'Zips' as far as I see, your script will not work / load any records from BillToAddress table.

Re: Very basic Where Exists question

swuehl — Fri, 04 Dec 2015 23:23:52 GMT

No, the first argument is stating a field name you want to check and the second is an (optional) expression (note that the expression could just reference another field name). See also the HELP for EXISTS() function:

These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record.

exists(field [ , expression ] )

Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.

Examples:

exists(Month, 'Jan') returns -1 (true) if the field value 'Jan' is found in the current content of the field Month.

exists(IDnr, IDnr) returns -1 (true) if the value of the field IDnr in the current record already exists in any previously read record containing that field.

exists (IDnr) is identical to the previous example.

Load Employee, ID, Salary from Employees.csv;
Load FirstName& ' ' &LastName as Employee, Comment from Citizens.csv where exists (Employee, FirstName& ' ' &LastName);
Only comments regarding those citizens who are employees are read.

Load A, B, C, from Employees.csv where not exists (A);
This is equivalent to performing a distinct load on field A.