Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

abhi1693r
New Contributor II

Counting excluding a string

Hi,

 I am counting 2 columns A and B, Count( DISTINCT A&'|'&B)

I want to exclude any values in column C with the prefix "ROM".

tried using 

=Count( DISTINCT {<C-={'ROM'}>} A&'|'&B)

 

Both Count( DISTINCT A&'|'&B) and Count( DISTINCT {<C-={'ROM'}>} A&'|'&B) gave me same result.

PS: An example for a value is 'ROM Truck Model'

Please help.

1 Solution

Accepted Solutions

Re: Counting excluding a string

Try this with wildmatch (*) within double quotes.... so instead of 'ROM', you would use "ROM*"

=Count( DISTINCT {<C -= {"ROM*"}>} A&'|'&B)

 

View solution in original post

9 Replies
lockematthewp
Contributor II

Re: Counting excluding a string

Count( DISTINCT {<C-={'ROM'}>} A&'|'&B) will only exclude rows where C = 'Rom' exactly. You need to use a wildcard search (*) to look for Rom anywhere in the word. Like: Count( DISTINCT {<C-={'*ROM*'}>} A&'|'&B) or Count( DISTINCT {<C-={'ROM*'}>} A&'|'&B) to only remove values that start with ROM.

Re: Counting excluding a string

Try this with wildmatch (*) within double quotes.... so instead of 'ROM', you would use "ROM*"

=Count( DISTINCT {<C -= {"ROM*"}>} A&'|'&B)

 

View solution in original post

abhi1693r
New Contributor II

Re: Counting excluding a string

Hey,

How can I have two conditions in this one line. Lets say ROM and COM.

Re: Counting excluding a string

This

=Count( DISTINCT {<C -= {"ROM*", "*COM*"}>} A&'|'&B)
abhi1693r
New Contributor II

Re: Counting excluding a string

What if "COM" is in another column, lets say 'P'?

abhi1693r
New Contributor II

Re: Counting excluding a string

Also, Is there a work around. In some of my cases, this formula does not work!

Help appreciated.

Re: Counting excluding a string

Not sure what you mean... can you share an example to show where it doesn't work?

abhi1693r
New Contributor II

Re: Counting excluding a string

Hi Sunny,

It will be difficult to give an example, but I will try.

I have table A LEFT JOIN(A) table B. 

Table A has a few extra rows which are added to the result after the commons are selected (Just how a left join works)

Now I am using =Count( DISTINCT {<C-={"ROM"}>} A&'|'&B) and the system is still counting those "extra rows" which got added from in Table A.

 

Now I think, the formula is working. But still I don't know why its not filtering properly.

 

Also tried, Count( DISTINCT {<C-={"ROM"}>} {<P-={"COM"}>} A&'|'&B)

Thanks for your patience.

abhi1693r
New Contributor II

Re: Counting excluding a string

I have a feeling it is filtering with ROM condition first and then applying A&'|'&B. Because some rows are getting removed when we use C-=ROM