Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

How to write an expression to show a 'complement' table (i.e. all values not in another table for a certain field)?


I want to have 2 straight tables:

Table 1:

Contains 1 field (Product_ID), and 1 expression based on Sales (e.g. sum(Sales)). My expression is actually much more complicated than this, though. Based on my selections of Product_ID, certain records of Product_ID (and corresponding Sales expression value) appear in the table (dependent on my expression).

Now I want to create a second table so that it holds all other records not shown in Table 1. Is there a way to do this? i.e. Is there a way to write an expression for the second table based solely on what is shown in Table 1 in a field, independent of user selections

i.e. user selections --> drives Table1 output --> then this drives Table2.

For example:

All possible Product_ID values = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

I select certain Product_ID values.

Based on my Table1 expression, Table1 shows records for Product_ID values = { 2, 3, 5, 6, 7, 10 }.

(Note, my selection is not neccesarily { 2, 3, 5, 6, 7, 10 })

And I want Table2 to show records for Product_ID = { 1, 4, 8, 9 }.

Basing Table2 on an expression driven by user selections but having an opposite effect to Table1 does not work in the case of my expressions - Therefore, I need the expression in Table2 to be essentially independent of user selections and to be like "Show records for Product_ID = Product_ID's not in Table 1).

Is there a way to do this?