Excluding 2 values in set analysis

[Anonymous]

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!

[olivierrobin]

hello

try   -=    instead of =-

[olivierrobin]

and instead of bot, use {'bot'}

[Anonymous]

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

[Anonymous]

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)

[sunny_talwar]

May be try this

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

[Anonymous]

I still couldn't get it correct.

Here is a test qvw

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

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

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

[sunny_talwar]

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)

or

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