Filtering / Reducing data using keep prefix

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.

Complete/Original Sales Table

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. 

Filter Table

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:

Final Sales Table (sales by salesperson)

Sales by SalesPerson

Attached is a qvw showing this QV functionality. In this example, the Original table, the filter table and the final table were loaded unassociated.

• Keep.qvw

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