Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try:
=Count(DISTINCT{<Status={1},RFQ={''}>}Besteknummer)
Or
=Count(DISTINCT{<Status={'1'},RFQ-={'*'}>}Besteknummer) // Note '-' sign
Try this
Count({<Status={1},RFQ={''}>}DISTINCT Besteknummer)
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
Hi Jonathan
You solution works. Thanks a lot.
Marc Van Rie
Hi Jonathan
You solution works. Thanks a lot.
Marc Van Rie
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)