How to split and filter the data?

[Peony]

Hi All

There is a table like this:

Lad *Inline: [

Id, Letter 
1,   A
2,  B
3,  C
4,  D
5,  E  ];

I need to create filter filed in UI that will allow to select data using next rule:

If letter =A, then select all letters except B. If letter = B, select all letters except A.
 
I cam implement this logic by splitting current table on two. First table will contain all data without rows related to the A letterer. Second table - the same for rows related to the B letter. Than concatenate both tables in one. 

But maybe there is some other ideas how to solve this? 

[ArnadoSandoval · Accepted solution]

Hi Peony,

I found a way to implement/resolve your question; you will need to use these features/knowledge: GetSelectedCount,  GetCurrentSelections  and PivotTable; also, this topic was of great help Dynamic Filter on a Pivot table Row-Qlik Sense (very useful information)

 First of all, I loaded the example data you provided, and introduce a Filter table with just two values, A and B, here is the load script:

NoConcatenate 
SplitData:
Load * Inline [
Id, Letter
1,  A
2,  B
3,  C
4,  D
5,  E  ];

NoConcatenate
Filter:
Load * Inline [
Filter
A
B
];

Now, on the user interface I added the Straight Table with the data you provide, a Selector for the Filter, a Textbox showing the selections (using GetCurrentSelections), a second Textbox showing the selected filters count (using GetSelectedCount), e.g. 1 if you select A or B, or 2 if you select A and B; finally the pivot table having two dimensions: Letter and Filter, here Filter is an expression, and the pivot table's measure is just the Id column.

The expression for the Filter column in the Pivot Table is shown below:

=if(GetSelectedCount(Filter, true) = 0 or GetSelectedCount(Filter, true)=2, Null,  If(Filter = 'A', if(Letter = 'A', Null, 'A'), if(Letter = 'B', Null, 'B')))

The expression for the measures column of the pivot table is just

=Id

The following 3 screenshots show the UI with no selections:

One selection (A):

And two selections:

Hope this helps,

Arnaldo Sandoval

[Peony]

Arnaldo, thank you so much for your help and detailed explanation of the solution! It is really great.