Exclude unwanted data in a column (pivot table)

[chris_gva]

Dear all,

Newby on Qlik sense, I am trying to exclude some dummy entries in my pivot.

In the example below, I would like to exclude see any "test" or "trial" entries.

I tried to add the following condition in the dimension condition "show the column if" : 
[Reference]<>'test' or [Reference] <>'trial'
the editor says the expression is fine (OK).

Example data :
Country|Reference|Quantity
Switzerland|test|20
Switzerland|GO1212|20
Switzerland|GO1111|45
Switzerland|trial|20
France|test|3
France|GM1215|2
France|GO1111|15
France|trial|45

thanks a lot for your help

[brunobertels · Accepted solution]

Hi 

may be this 

aggr(if(match(Reference,'test','trial'),null,Reference),Reference)

[BrunPierre]

Alternatively, this at the back end

TempData:
load * Inline [
Country,Reference,Quantity
Switzerland,test,20
Switzerland,GO1212,20
Switzerland,GO1111,45
Switzerland,trial,20
France,test,3
France,GM1215,2
France,GO1111,15
France,trial,45];

NoConcatenate
Data:
LOAD *
Resident TempData
Where not WildMatch(Upper(Reference),'*TEST*','TRIAL');
DROP Table TempData;

[chris_gva]

I'll dive into this match function, but do I understand Qlik well if I do not have to use aggr here ? In my pivot the reference is used as a column dimension, hence no aggregation needed.

I'll let you know, at the moment all my trials are returning errors in the function editor...

Tks anyway 🙂

[chris_gva]

Tks for the idea, this would be my last option.

Load of the data is done by another team, and this solution would oblige me to change the query if a new "fake" occurrence appear.

Having it solved by function, allow me to be more flexible :).

Cheers,

[BrunPierre]

If you're sticking to your approach then this

=IF([Reference]<>'test' and [Reference] <>'trial',[Reference])

Also, you'll have to suppress when the value is null.

[brunobertels]

Hi

Aggr is'nt necessary 

You can avoid it like this 

=if(not match(Reference,'*TEST*','TRIAL'),Reference)

Or

if(match(Reference,'*TEST*','TRIAL'),null(),Reference) 

see help page for understanding match not match mismatch function in QS