Lookup functions Similar to the joins are the lookup functions. These functions fetch information from another table or look up a specific value in another table and return this. One case is a mapping or a translation: You could for instance have a product ID and you want to translate that into a specific product attribute. In other words – you never expect more than one value for the specific product ID. If there are several possible values in the lookup table, the lookup function will just take the first one found. Another case is when you want to load all records from one table, but exclude the records that have an ID listed in another table. If you want to solve these types of problem using SQL, you would most likely use joins. However, in QlikView, you should avoid joins in these situations. Instead you should use one of the lookup functions; Applymap() is usually the best choice. This way you get a faster script execution and you ensure that the number of records do not change when they shouldn’t. If you do expect several values for each of the input values, you should not use a lookup function to solve the problem. The Lookup() function The most obvious lookup function is the function with the same name. With it, you can look into another previously loaded table and retrieve a specific field value. 8 For example, you may while loading a table containing order data want to retrieve the product category for the product found in the order data. You have the product ID in the order data table, but the product category is found in the product table. Then you could solve this problem by using the Lookup() function: Lookup ('ProductCategory', 'ProductID', ProductID, 'ProductTable') The Lookup() function will then return the value of the field ProductCategory (first parameter) in the table ProductTable (fourth parameter) from the record where the field ProductID (second parameter) has the same value as the field ProductID in the order data table (third parameter). Note that references to fields in other tables must be enclosed by single quotes, whereas references to fields in the same table must not be enclosed by single quotes. In this hypothetic example, the Lookup() function is used inside the Load statement of the order data, so fields in the product table must be enclosed by single quotes. The advantage with the Lookup() function is that it is flexible and can access any previously loaded table. The drawback is that it is slow compared to the Applymap() function.
Lookup functions Similar to the joins are the lookup functions. These functions fetch information from another table or look up a specific value in another table and return this. One case is a mapping or a translation: You could for instance have a product ID and you want to translate that into a specific product attribute. In other words – you never expect more than one value for the specific product ID. If there are several possible values in the lookup table, the lookup function will just take the first one found. Another case is when you want to load all records from one table, but exclude the records that have an ID listed in another table. If you want to solve these types of problem using SQL, you would most likely use joins. However, in QlikView, you should avoid joins in these situations. Instead you should use one of the lookup functions; Applymap() is usually the best choice. This way you get a faster script execution and you ensure that the number of records do not change when they shouldn’t. If you do expect several values for each of the input values, you should not use a lookup function to solve the problem. The Lookup() function The most obvious lookup function is the function with the same name. With it, you can look into another previously loaded table and retrieve a specific field value. 8 For example, you may while loading a table containing order data want to retrieve the product category for the product found in the order data. You have the product ID in the order data table, but the product category is found in the product table. Then you could solve this problem by using the Lookup() function: Lookup ('ProductCategory', 'ProductID', ProductID, 'ProductTable') The Lookup() function will then return the value of the field ProductCategory (first parameter) in the table ProductTable (fourth parameter) from the record where the field ProductID (second parameter) has the same value as the field ProductID in the order data table (third parameter). Note that references to fields in other tables must be enclosed by single quotes, whereas references to fields in the same table must not be enclosed by single quotes. In this hypothetic example, the Lookup() function is used inside the Load statement of the order data, so fields in the product table must be enclosed by single quotes. The advantage with the Lookup() function is that it is flexible and can access any previously loaded table. The drawback is that it is slow compared to the Applymap() function.
