topic Re: How to use Not(WildMatch()) in a pivot table expression in QlikView

How to use Not(WildMatch()) in a pivot table expression

Tue, 07 Feb 2017 20:32:02 GMT

Hey guys! I am totally stuck. I typically would use this statement in the script level which works fabulously. However, my end user now wants to include data in a different location. In the tables where I DON'T want the data to show I need to use the equivalent of

Not(WildMatch()).

Here is my formula:

//=Sum(
// If(Account='Subcontracts/CWTs',[Cost Amount],
// If(Account='Touch/Material' AND (CCP=857 or CCP=858 or CCP=834 or CCP<>799) AND ([Hrs/Qty]>=1 OR [Hrs/Qty]<0) AND Not (Wildmatch([Document Description],'*MAT*OH*')) , [Cost Amount],If((Account='Touch/Material' or Account='Subcontracts/CWTs') AND Not(CCP=857 OR CCP=858 OR CCP=834 OR CCP=799) AND (BP<>'12345' OR BP<>'67891'),[Cost Amount],Null()
// )//End Touch/Material
// )//Remove Material Overhead
//)//End Subcontracts/CWTs ODC
//)//EndSum

The text in red is my problem child... help!

Re: How to use Not(WildMatch()) in a pivot table expression

swuehl — Tue, 07 Feb 2017 20:59:06 GMT

Does your real expression also show the commented parts using // ?

If possible, post a small sample QVW that demonstrates your problem (and add a description of what you expect).

Re: How to use Not(WildMatch()) in a pivot table expression

Tue, 07 Feb 2017 21:01:43 GMT

No. It was commented out where I was trying other options.. This is the output that I am trying to get rid of

Re: How to use Not(WildMatch()) in a pivot table expression

vishsaggi — Tue, 07 Feb 2017 21:07:37 GMT

May be try this ?

AND Not Wildmatch([Document Description],'* MAT *OH*'))

Re: How to use Not(WildMatch()) in a pivot table expression

swuehl — Tue, 07 Feb 2017 21:08:47 GMT

You are using the Wildmatch() function to filter your record set used in a Sum() aggregation function.

Not sure how your image relates to that. Do you want to hide certain combinations of dimension values?

You know how you can do this? For example using calculated dimensions or set analysis?

Re: How to use Not(WildMatch()) in a pivot table expression

Tue, 07 Feb 2017 21:17:06 GMT

I don't understand what you are asking. I have a set of records with document descriptions, document numbers and costs. I would normally just put the Not(WildMatch()) in the script so that no records show where document description contains *MAT*OH* where * is the wildcard.

The problem is that my end user wants to see that data in some cases. This means i'm stuck trying to get rid of it in the expression level. So I need one table where all the documents show where the description does not contain *MAT*OH* and I need one table to show all documents where it DOES contain *MAT*OH*.

Re: How to use Not(WildMatch()) in a pivot table expression

Tue, 07 Feb 2017 21:17:47 GMT

The reason I am summing is because this is all based off of an end cost amount.

Re: How to use Not(WildMatch()) in a pivot table expression

swuehl — Tue, 07 Feb 2017 21:28:55 GMT

Not sure I understood, but maybe try a calculated dimension like

=If( Wildmatch([Document Description],'*MAT*OH'), [Document Description])

resp.

=If( Wildmatch([Document Description],'*MAT*OH')=0, [Document Description])