topic Re: counting with if-expression in QlikView

counting with if-expression

Thu, 17 Jul 2014 08:11:44 GMT

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

Re: counting with if-expression

MK_QSL — Thu, 17 Jul 2014 08:14:21 GMT

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

Re: counting with if-expression

puttemans — Thu, 17 Jul 2014 08:21:08 GMT

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

Re: counting with if-expression

Anonymous — Thu, 17 Jul 2014 08:30:02 GMT

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)

Re: counting with if-expression

Thu, 17 Jul 2014 08:32:46 GMT

I added the attachment

Re: counting with if-expression

Anonymous — Thu, 17 Jul 2014 08:40:13 GMT

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))

Re: counting with if-expression

puttemans — Thu, 17 Jul 2014 08:46:53 GMT

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