Problem Set Analysis

[#1]

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

[tresesco]

Try:

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

Or

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

[ashwanin]

Try this

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

[jonathandienst]

Hi

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

[#5]

Hi Jonathan

You solution works. Thanks a lot.

Marc Van Rie

[#6]

Hi Jonathan

You solution works. Thanks a lot.

Marc Van Rie

[#7]

Hello

One off the solutions proposed by Jonathan Dienst and working:

Thanks a lot.

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)