There are plenty features of QlikView that are either underrated or many users don’t know about them at all. The Keep prefix is one of those QlikView hidden treasures.
When filtering tables according to data contained in another table, there’s a well-known function called exists(). However, this post demonstrates that there are some cases when exists() function can lead to unexpected / wrong results.
This is where Keep prefix becomes handy (furthermore, an alternative solution to a common issue). Let’s take the following requirement as example:
Display the sales amount per Sales Persons but only for those who have “active” status
The complete sales table includes data for all Sales Persons, including active and inactive ones (in this case the Sales fact table contains historical data). However, there is no field that tells the current status of every Sales Person.
Luckily, this company has a filtered table that includes only active Sales Persons and that will be useful for displaying just the information that is relevant.
I think the best way of explaining how Keep Prefix works is that it is just like using Join Prefix but without merging different tables. So it will look for coincidences in Table A and Table B (left, right and inner prefixes can also be used) and return a set or records but without joining them, how awesome is that?
That being said, the solution of this requirement is to use Keep Prefix to filter an original/complete table according to some criteria (in this case, displaying sales related to salespersons only). Besides, the filter table can be stored in a QVD file so it can be reutilized any time this business criteria needs to be applied:
Attached is a qvw showing this QV functionality. In this example, the Original table, the filter table and the final table were loaded unassociated.
It is important to understand, that while QlikView and SQL may seem similar at first, they are very different in terms of logic and structure. Once, this is well understood, advantages of both technologies can be used in favor of creating valuable dashboards to take vital decisions,
Stay tuned for next post