topic Expressions in QlikView

Expressions

Anonymous — Wed, 18 Nov 2015 14:06:25 GMT

**Update**

I have this straight table expression ==count({<Date = {[<>NULL]}>}distinct ID)

I really need to understand what is being counted and how would I write the above expression in the script editor, something like this, IF(date <> 'NULL', 1,0) as DateCount? (Where would I add the distinct ID)

Re: Expressions

Wed, 18 Nov 2015 14:28:34 GMT

Re: Expressions

Colin-Albert — Wed, 18 Nov 2015 14:34:41 GMT

The expression is showing a distinct count of ID, for rows in the current dataset where the Date is not null

Re: Expressions

Anonymous — Wed, 18 Nov 2015 14:35:31 GMT

Thanks, I've updated my question to be more clear. Would you mind taking a look?

Re: Expressions

Anonymous — Wed, 18 Nov 2015 14:36:44 GMT

How would that look in the script editor?

Re: Expressions

NareshGuntur — Wed, 18 Nov 2015 14:43:19 GMT

Hi John,

I think there is an issue with the expression.

<>NULL will not be treated as "not equals to". It should be written as Date =- {[NULL]}.

Here, it will consider only the greater than symbol.

Cheers,

Naresh

Re: Expressions

Colin-Albert — Wed, 18 Nov 2015 14:45:34 GMT

It depends on your data model.

Are the Date fields and ID fields in the same table?

If yes, then you could add this to your load script if(Date, 1) as DateCount

then use sum(DateCount) in your charts

Re: Expressions

settu_periasamy — Wed, 18 Nov 2015 14:48:19 GMT

Hi,

Do you have Null Value in your Date Field? Then, you can try this in the script, Like

if(isNull(Date) or Len(Trim(Date))>0,1,0) as DateCount

in Expression something like..

=count(Distinct {<DateCount={1}>} ID)

=Sum(Distinct DateCount)

Re: Expressions

Anonymous — Wed, 18 Nov 2015 14:57:20 GMT

Null is the acutal value in the field.

My straight table Expression is ==count({<Date = {[<>NULL]}>}distinct ID)

Really wanting to write this out in the scrip editor and just sum in the charts.

Something list this...IF(date <> 'NULL', 1,0) as DateCount? Will this work and does it take into account the "distinct ID" piece of the first expression?

Re: Expressions

Anonymous — Wed, 18 Nov 2015 14:58:37 GMT

Okay on the right track! There are not null values but there is a value called "NULL"

Re: Expressions

settu_periasamy — Wed, 18 Nov 2015 15:05:12 GMT

Ok then, your expression if(date <> 'NULL',1,0) as DateCount looks fine for me.

Try the Expression : Sum(Distinct DateCount)