topic Re: Problem Set Analysis in QlikView

Problem Set Analysis

Wed, 26 Nov 2014 05:34:07 GMT

Hello

I have a problem in an expression. In only want to show the records (=Besteknummer) where field Status = 1 and the field "RFQ" is empty.

In my expression the set analysis is : =Count(DISTINCT{<Status={'1'},RFQ={0}>}Besteknummer)

It doesn't work.

RFQ

Vestiging (code)

Besteknummer

Status

6000132826	240P	5753414011	1
-	-	7152513_44	1

Can somebody help me ?

Thanks in advance

Marc Van Rie

Re: Problem Set Analysis

tresB — Wed, 26 Nov 2014 05:38:24 GMT

Try:

=Count(DISTINCT{<Status={1},RFQ={''}>}Besteknummer)

=Count(DISTINCT{<Status={'1'},RFQ-={'*'}>}Besteknummer) // Note '-' sign

Re: Problem Set Analysis

ashwanin — Wed, 26 Nov 2014 05:40:51 GMT

Try this

Count({<Status={1},RFQ={''}>}DISTINCT Besteknummer)

Re: Problem Set Analysis

jonathandienst — Wed, 26 Nov 2014 05:48:11 GMT

Just like you cannot select null values from a list box, you cannot use set analysis to select null values. You will either need to use a count(if()) expression, set a flag in the load script for the null values or replace the null values with blank strings or a value like 'MISSING'. In the last two options, you can use set analysis to select the flag or the 'MISSING' values.

count(If()):

=Count(If(IsNull(RFQ), DISTINCT{<Status={'1'}>} Besteknummer))

flag:

LOAD ...

RFQ,

If(IsNull(RFQ), 1, 0) As RFQNull,

...

=Count(DISTINCT{<Status={1},RFQNull={'1'}>}Besteknummer)

missing:

LOAD ...

If(IsNull(RFQ), 'MISSING', RFQ) As RFQ,

...

=Count(DISTINCT{<Status={1},RFQ={'MISSING'}>}Besteknummer)

HTH

Jonathan

Re: Problem Set Analysis

Wed, 26 Nov 2014 11:11:03 GMT

Hi Jonathan

You solution works. Thanks a lot.

Marc Van Rie

Re: Problem Set Analysis

Wed, 26 Nov 2014 11:12:13 GMT

Hi Jonathan

You solution works. Thanks a lot.

Marc Van Rie

Re: Problem Set Analysis

Wed, 26 Nov 2014 11:18:13 GMT

Hello

One off the solutions proposed by Jonathan Dienst and working:

Thanks a lot.

count(If()):

=Count(If(IsNull(RFQ), DISTINCT{<Status={'1'}>} Besteknummer))

flag:

LOAD ...

RFQ,

If(IsNull(RFQ), 1, 0) As RFQNull,

...

=Count(DISTINCT{<Status={1},RFQNull={'1'}>}Besteknummer)

missing:

LOAD ...

If(IsNull(RFQ), 'MISSING', RFQ) As RFQ,

...

=Count(DISTINCT{<Status={1},RFQ={'MISSING'}>}Besteknummer)