topic Re: Excluding 2 values in set analysis in QlikView

Excluding 2 values in set analysis

Anonymous — Fri, 18 May 2018 15:48:31 GMT

Hi everyone,

I have looked in multiple questions and none of the answers for excluding has worked for me.

Below is the expression I am testing.

I want to count all the unique emails and not include the two fields with values as bot.

count({<locale=- {bot}, signup_source =-{bot} >} DISTINCT email)

I hope this makes sense.

Thanks!

Re: Excluding 2 values in set analysis

olivierrobin — Fri, 18 May 2018 15:51:56 GMT

hello

try -= instead of =-

Re: Excluding 2 values in set analysis

olivierrobin — Fri, 18 May 2018 15:52:46 GMT

and instead of bot, use {'bot'}

Re: Excluding 2 values in set analysis

Anonymous — Fri, 18 May 2018 15:58:06 GMT

do you want to exclude only if locale = bot AND signup_source = bot?

Re: Excluding 2 values in set analysis

Anonymous — Fri, 18 May 2018 16:05:51 GMT

Some emails don't have either value and some have either one or both values.

I just want to not show it in the total at all. I was going to exclude it in the load but I run a separate report on how many bots we have too.

I changed my set expression to the one Olivier recommended but it doesn't give me the correct answer. It is way to low of a number

count({<locale-= {'bot'}, signup_source -={'bot'} >} DISTINCT email)

Re: Excluding 2 values in set analysis

sunny_talwar — Fri, 18 May 2018 16:06:54 GMT

May be try this

Count({<locale = -{bot}>+<signup_source = -{bot}>} DISTINCT email)

Re: Excluding 2 values in set analysis

Anonymous — Fri, 18 May 2018 16:29:29 GMT

I still couldn't get it correct.

Here is a test qvw

My data is from Redshift but I used excel to simulate this.

Re: Excluding 2 values in set analysis

sunny_talwar — Fri, 18 May 2018 16:32:16 GMT

Try this

=Count({<email = {"=locale <> 'bot' or signup_source <> 'bot'"}>} DISTINCT email)

Re: Excluding 2 values in set analysis

Anonymous — Fri, 18 May 2018 18:12:57 GMT

Thanks for this info. It works great in the example qvw but not in mine.

I will keep this question open for now and see what is so different in the orginal qvw.

Thanks

Re: Excluding 2 values in set analysis

sunny_talwar — Fri, 18 May 2018 18:17:24 GMT

Sure... in the mean time you can test out another method of creating a flag in the script

LOAD email,

locale,

signup_source,

If(locale <> 'bot' or signup_source <> 'bot', 1, 0) as Flag

FROM

bottest.xlsx

(ooxml, embedded labels);

Now you can either do this

Sum(Flag)

Count(DISTINCT {<Flag = {1}>} email)

Re: Excluding 2 values in set analysis

Anonymous — Tue, 22 May 2018 17:46:48 GMT

Hi Sunny,

Everything works great now.

I forgot to change the field name to matchup to my field name.

Also I made one slight change to your expression. I changed the or to an and

=Count({<email = {"=locale <> 'bot' and signup_source <> 'bot'"}>} DISTINCT email)

That gives me a more accurate answer.

As always I appreciate your assistance