Very basic Where Exists question

[kschramm]

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

[sunny_talwar]

Try this:

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

[kschramm]

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? 

[#4]

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.

[swuehl]

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.

[swuehl]

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.