topic Blank values causing set analysis issue in QlikView

Blank values causing set analysis issue

Mon, 02 Jun 2014 16:44:17 GMT

I have the following expression

=COUNT(DISTINCT {$<Firm-={"ICAP","*TEST*"}>}SessionId)

Unfortuantely records where the Firm field is blank are not being counted. How can i get them counted, whilst simutaneously ignoring the two values stated above?

Re: Blank values causing set analysis issue

Mon, 02 Jun 2014 16:52:19 GMT

Maybe try....
=COUNT(DISTINCT {$<Firm-={"ICAP","*TEST*"}, Firm={*}>}SessionId)

Re: Blank values causing set analysis issue

Mon, 02 Jun 2014 16:55:19 GMT

no luck

Re: Blank values causing set analysis issue

Mon, 02 Jun 2014 16:57:19 GMT

Are you able to post a sample of the data or QVW?

Re: Blank values causing set analysis issue

Nicole-Smith — Mon, 02 Jun 2014 17:03:05 GMT

You should have single quotes around the values instead of double quotes:

=COUNT({$<Firm-={'ICAP','*TEST*'}>}DISTINCT SessionId)

Re: Blank values causing set analysis issue

Mon, 02 Jun 2014 17:13:51 GMT

=COUNT(DISTINCT {$<Firm-={"ICAP","*TEST*"}> + <Firm=p(1-$<Firm={"*"}>Firm)> }SessionId)

Re: Blank values causing set analysis issue

Mon, 02 Jun 2014 17:23:09 GMT

I just created a sample file and it works. I did get the blank value count included. May be you want to post a sample showing data? I have attached my sample file for your ref.

Re: Blank values causing set analysis issue

eduardo_sommer — Mon, 02 Jun 2014 17:34:17 GMT

Hi Alex,

Is your value really blank? Make sure it is not null.

Eduardo

Re: Blank values causing set analysis issue

Tue, 03 Jun 2014 07:05:05 GMT

Thanks Nicole. I tried this but it still didn’t work.

Out of interest, what’s the difference between apostrophies and speech marks?

Re: Blank values causing set analysis issue

Tue, 03 Jun 2014 07:09:44 GMT

That's a great point Eduardo. The values are showing as -

Does that mean null or blank? What difference would this make to my expression?

Re: Blank values causing set analysis issue

Tue, 03 Jun 2014 07:11:02 GMT

A variation of this worked for me, but I can't help feel the expression could be slightly more straightforward

Re: Blank values causing set analysis issue

Nicole-Smith — Tue, 03 Jun 2014 22:00:01 GMT

Single quotes are used for strings, double quotes are used for field names.