Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Where is the attachment? Kindly provide little sample data or sample qvw
Please provide some more info. Why are you using 'distinct'? If you're getting too little results, it may be due to this function.
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)
I added the attachment
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))
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