counting with if-expression

[#1]

In attatchment a printscreen of the data I have.

The emptyings_error fiels contains text, nothing or a dash.

I need to count the emptyings_id where text is filled in and the emptyings_id where no text is filled in (dash and nothing in the field)

The next two expressions I used to count the no-text fields:

count(distinct(if(match(emptyings_error,''), emptyings_id)))

-> to little results

count(distinct(if(match(emptyings_error,'NULL'), emptyings_id)))

->no results, wrong expression.

The expression used to count the text-filled fields:

count(distinct(if(match(emptyings_error,'%'), emptyings_id)))

count(distinct(if(match(emptyings_error,'*'), emptyings_id)))

->neither works

Can any help me write the correct expression?

Kind regards,

Katleen

[MK_QSL]

Where is the attachment? Kindly provide little sample data or sample qvw

[puttemans]

Please provide some more info. Why are you using 'distinct'? If you're getting too little results, it may be due to this function.

[Anonymous]

Hi,

Try below,

When emptyings_error field is null

=count({$<emptyings_error={"=isnull(emptyings_error)"}>} distinct emptyings_id)

When emptyings_error field has value

=count({$<emptyings_error={"*"}>} distinct emptyings_id)

[#5]

I added the attachment

[Anonymous]

So you have null as well as blank value in your dataset, in that case you can use Len() function to calculate ids. See below,

=Count(Distinct If(Len(Trim(emptyings_error))=0, emptyings_id))

[puttemans]

Hello Katleen,

Why not work with an identifier first? You make an additional varaibale in the script, a numerical flag, which combines the dashes and the spaces into one.

if(emptyings_error = '-',1,(if(emptyings_error = ' ',1,2))) as Flag_emptyings_error,

Once created, you can use a simple count

count(if(Flag_emptyings_error = 1), emptyings_id) for the dashes and spaces

count(if(Flag_emptyings_error = 2), emptyings_id) for the text