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;
