Exclude values from dimension B from dimension A

[ZhangYixin]

Hi experts:

I am working on a simple Qlik cloud app. The problem I have is I want to exclude the value of one dimension from another.

Assume we have table A and B:

Category	ComponentID	Desc
a	A1	a1
a	A2	a2
a	A3	a3
a	A4	a4
a	A5	a5
b	A1	a1
b	A2	a2
b	B1	b1
b	B2	b2
b	B3	b3
c	A1	a1
c	A2	a2
c	B1	b1
c	C1	c1
c	C2	c2

Table A and B have the same data.

In the frontend there are two filters which is connected to the Category dimension of table A and B respectively.

So when the user select filter A for Category a, filter B for Category c, a straight table will show all ComponentID and Desc which exists in filtered Table A but not in filtered Table B:

ComponentID	Desc
A3	a3
A4	a4
A5	a5

Sounds pretty simple. I just want a exclusion set of A - B.

So I used this for the ID column of the result table: 

• if([a.ComponentID]<>[b.ComponentID], [a.ComponentID]) 

• if(not match([a.ComponentID],[b.ComponentID]), [a.ComponentID]

• ({<[a.ComponentID]-=[b.ComponentID]>} [a.ComponentID])

None of them work. Both of them returns all values under Category a.

Could you help me with this?

[Aditya_Chitale]

I have multiple questions:

1. In your, provided data, do both tables have  same data set ? I am not talking about field names but actual values. Say for eg, do both tables have a, b & c values in Category field ? Or a & b come from table A and c come from table B which is then auto concatenated to show as single table in frontend ?

If answer to above question is yes:

1. If both tables contain exactly same fields & data, how did you get 2 separate filters for each table to  work at the same time ?

If possible, can you share your data model along with sample data (jumble the numbers if you want)

Regards,

Aditya

[ZhangYixin]

1. Yes. The two tables A and B are copied from one fact table, which gets data from a SQL query.

2. The user wants to compare the component in two user-selected category, A and B. The user wants to see what is in category A but not in category B, what is category in B bot not in category A, and what is in both category A and B.

The demo data model:

[src]:
Load * Inline
[
SKU, ComponentID, Desc
a, A1, A1
a, A2, A2
a, A3, A3
a, A4, A4
a, A5, A5

b, A1, A1
b, A2, A2
b, B1, B1
b, B2, B2
b, B3, B3

c, A1, A1
c, A2, A2
c, B1, B1
c, C1, C1
c, C2, C2
];

Qualify *;
[a]:
Load *
Resident src;

[b]:
Load *
Resident src;

drop table src;

The question is always the same: How to get exclusion set/difference set using chart expression?

Here I also include a screenshot of the front end. The user will choose the filter panes on top to select category A and B, and the result will show in the three tables below.

[ZhangYixin · Accepted solution]

Hi all:

I figured out the answer:

=if (not isnull(aggr(count( {<[Before.ComponentID] = P([Before.ComponentID]) - P([After.ComponentID])>} [Before.ComponentID]), [Before.ComponentID])) , [Before.ComponentID], Null())

Can be further simplified but I am good with this.