topic Re: 'if' statement works in straight table but not Text object in QlikView

'if' statement works in straight table but not Text object

Thu, 29 May 2014 05:27:46 GMT

Hi,

My data contains Accounts, Tasks and Delivered Status. The status can be "delivered" (1), "not delivered" (-1) or "not actioned" (0). I want a count of all the accounts that ONLY have "not actioned" or blank. In other words i'm trying to find accounts that have zero activity. My thought was to count delivered and not delivered - if this was zero, then count that account.

My expression is as follows:

=if (count ({<Delivered -= '0')>} distinct Accounts) = 0, count (Accounts), 0)

This works in a straight table and it lists the accounts. However, I want it represented as a single number via Text Box which returns a 0 (zero).

Should i be using another expression?

Thanks for any help.

Cheers.

Re: 'if' statement works in straight table but not Text object

tresB — Thu, 29 May 2014 05:56:39 GMT

May be simply = Count ({<Delivered-={1,-1}>} Distinct Accounts) ?

Re: 'if' statement works in straight table but not Text object

MayilVahanan — Thu, 29 May 2014 05:56:50 GMT

Try like this

= Sum(Aggr(if (count ({<Delivered -= '0')>} distinct Accounts) = 0, count (Accounts), 0), Dim1))

Note:

Dim1 is the dimension used in the straight table.

Re: 'if' statement works in straight table but not Text object

Thu, 29 May 2014 06:08:02 GMT

Hi,

You can use simply like

Count ({<Delivered-={1,-1}>} Distinct Accounts)

Regards

Deepak

Re: 'if' statement works in straight table but not Text object

jonathandienst — Thu, 29 May 2014 06:19:26 GMT

Is the 'blank' Delivered Status a null? If it is an empty string, then tresesco's expression should meet your requirements.

If it is null, then you have a problem. Set expressions work the same as selections from the front end, and you cannot select null values. In the same way, you cannot select null values using a set expression. So {<Delivered-={1,-1}>} will return the zeroes, but not the nulls.

You can use Sum(If()), like this:

Sum(Distinct If(Delivered = 0 Or IsNull(Delivered), Accounts, 0))

This will select nulls, but Sum(If()) is a performance killer in larger data sets. So if you cannot use Sum(If()), then you have two options:

Apply NullAsValue to the field. Then {<Delivered-={1,-1}>} will select the nulls (now blanks) and 0's

Before the LOAD, add the line:

NULLASVALUE Delivered;

Set a flag to indicate the null value, and use this flag in the expression. Something like:

LOAD ....

Delivered,

If(IsNull(Delivered) Or Delivered = 0, 1, 0) As DeliveredNoAction,

...

Now your expression is Sum({<DeliveredNoAction={1}>} Distinct Accounts)

HTH

Jonathan

Re: 'if' statement works in straight table but not Text object

sasikanth — Thu, 29 May 2014 06:45:44 GMT

Try like this

=if (count ({<Delivered ={*}- {0})>} distinct Accounts) = 0, count (Accounts), null())

Re: 'if' statement works in straight table but not Text object

Fri, 30 May 2014 00:25:54 GMT

Hi,

Thanks! This worked.

Cheers.