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;