topic Re: Counting excluding a string in QlikView

Counting excluding a string

abhi1693r — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Counting excluding a string

lockematthewp — Thu, 18 Jul 2019 16:53:31 GMT

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

sunny_talwar — Thu, 18 Jul 2019 16:54:39 GMT

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

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

Re: Counting excluding a string

abhi1693r — Mon, 22 Jul 2019 16:07:52 GMT

Hey,

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

Re: Counting excluding a string

sunny_talwar — Mon, 22 Jul 2019 17:04:27 GMT

This

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

Re: Counting excluding a string

abhi1693r — Fri, 26 Jul 2019 14:24:48 GMT

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

Re: Counting excluding a string

abhi1693r — Fri, 26 Jul 2019 14:25:55 GMT

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

Help appreciated.

Re: Counting excluding a string

sunny_talwar — Fri, 26 Jul 2019 14:47:23 GMT

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

Re: Counting excluding a string

abhi1693r — Fri, 26 Jul 2019 19:01:34 GMT

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.

Re: Counting excluding a string

abhi1693r — Fri, 26 Jul 2019 19:06:00 GMT

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