How can I exclude selected values?

[reporting_neu]

I have the following problem. I have a table (Table1) with part numbers (Part1) and BOM number (Bom1). In addition, another table (Table2) that is identical to Table1. Here "Part2" and "Bom2". Table1 and Table2 are connected via "Bom1" and "Bom2" (key).

Now I represent this as follows in Qlik:

On the left a table with only "Part1". (I'll call the table "A")
Next to it a table with only "Bom1". (B)
On the far right a table with Part2. (C)

Like:
Table A

Table1.Part1

1001

1002

Table B

Table1.Bom1

9000

9001

Key between Table1 and Table2: Table1.Bom1 - Table2.Bom2

Table C

Table2.Part2

1001

1002

If I make a selection for "Part1" in the first table (A), I get all the parts lists (Bom1) for the selected part in table B. On the right side (C) I get all the parts (Part2) where the parts list is also present.

So far it works very well.

But I have the following problem, which I would like to solve.
If I select a part (Part1) in the first table (A), it also appears in table C. But I don't want that because I marked it in table A.

I tried to exclude the value with the following formula in C:
if(GetSelectedField(Table1.Part1) = Table2.Part2, null(), Table2.Part2)
This also works wonderfully as long as I only select one value in the left table (A). If I select more than one value, it no longer works because the "GetSelectetField" value then contains multiple values ​​and can no longer match Part2.
Also it doesn't work with if(Table1.Part1 = Table2.Part2, null(), Table2.Part2).

How can I exclude the selected value from table A from table C? Is there a specific function for this?

I hope you can help me.

[Andrea_Bertazzo]

Hi Reporting_neu,

Thanks for the post.

I am not sure I understand your explanation completely, but, in general you can use the identifier {1} in the measures to exclude all the selections.  For example:  Sum( {1} Sales ) calculates the sum of Sales independently of the selections in the app.

Have a look at this article for more information.

[reporting_neu]

Hi Andrea, thank you for your answer.
The problem is that in the spreadsheet I only have dimensions and no measures.

I couldn't find anything of the sort for dimensions.

The problem is also that I am not allowed to exclude all selections. Table C should display the values depending on table B. However, the values in Table C should not contain the selections from Table A.
I know it's complicated. 😁

[Andrea_Bertazzo · Accepted solution]

Hi @reporting_neu ,

Thanks for clarifying. I think the solution for your case is to use alternate states. Have a look here.

Alternate States exists also for QlikView .

[reporting_neu]

I had thought about that too. But the alternative state closes works independently and the dependency among the tables is lost. I am afraid that there is no solution to my request.

[Andrea_Bertazzo]

Hi @reporting_neu 

I have the same feeling. In these cases, it is good to re-think the design and imagine a new way to show the data.