topic Re: Exclude unwanted data in a column (pivot table) in App Development

Exclude unwanted data in a column (pivot table)

chris_gva — Fri, 26 Aug 2022 09:49:06 GMT

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).

thanks a lot for your help

Re: Exclude unwanted data in a column (pivot table)

brunobertels — Fri, 26 Aug 2022 09:56:47 GMT

may be this

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

Re: Exclude unwanted data in a column (pivot table)

BrunPierre — Fri, 26 Aug 2022 11:01:22 GMT

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;

Re: Exclude unwanted data in a column (pivot table)

chris_gva — Fri, 26 Aug 2022 11:04:56 GMT

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 🙂

Re: Exclude unwanted data in a column (pivot table)

chris_gva — Fri, 26 Aug 2022 11:07:13 GMT

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,

Re: Exclude unwanted data in a column (pivot table)

BrunPierre — Fri, 26 Aug 2022 11:21:50 GMT

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.

Re: Exclude unwanted data in a column (pivot table)

brunobertels — Fri, 26 Aug 2022 11:52:19 GMT

Aggr is'nt necessary

You can avoid it like this

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

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

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