Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
I have multiple questions:
If answer to above question is yes:
If possible, can you share your data model along with sample data (jumble the numbers if you want)
Regards,
Aditya
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.
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.