QlikView Exists Function

One QlikView function I use often in the loadscript is the Exists function. You give it a value or expression and the name of a field and it tells you if the value already has been loaded into the field. The function returns a True or False so it can be used in the Where clause of a Load statement or in an If statement. There are a lot of uses for the function and below are three examples that I’ve used recently. For the first example, I use it near the end of the loadscript when I am loading master data to match data already loaded in previous tables. Here’s what the code might look like when loading a product description to go along with previously loaded product code values:

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

That piece of code will only load product descriptions for product codes that were already loaded in previous tables. Note that the Exists function is a QlikView function so it is used as part of the QlikView 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 name, 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;