topic Re: How to include only those records that has a null Value in QlikView

How to include only those records that has a null Value

Anonymous — Fri, 17 Apr 2015 23:16:50 GMT

I have a straight table. I want the table to display metrics only for values that do not have an expiration date. Can I do that with a calculated dimension. I tried =if([Expiration date] ='-', [Expiration date]) and hide the column. Its not working. pls advise.

Re: How to include only those records that has a null Value

sunny_talwar — Sat, 18 Apr 2015 00:55:43 GMT

Try this expression:

=Sum({<[Expiration date] = e([Expiration date] = {'*?'})>} Cost)

I think this should work.

Best,

Sunny

Re: How to include only those records that has a null Value

robert_mika — Sat, 18 Apr 2015 01:18:44 GMT

You do not need extra dimension

only({1-<ExpirationDate={*}>} Cost)

or without Expiration date dimension:

Re: How to include only those records that has a null Value

ramoncova06 — Sat, 18 Apr 2015 03:00:50 GMT

here is another alternative

Sum({$<ProductID={"=nullcount(ExpirationDate)>0"}>} Cost )

this is based out of HIC document

https://community.qlikview.com/docs/DOC-3155/version/7

Re: How to include only those records that has a null Value

anbu1984 — Sat, 18 Apr 2015 06:25:56 GMT

Sum(If(IsNull(ExpryDt), Cost))

Sum({<ProdID={'=IsNull(ExpryDt)'}>}Cost)

Re: How to include only those records that has a null Value

Anonymous — Sat, 18 Apr 2015 16:39:55 GMT

Robert, the function I am using is not sum in my app. It is a filter that I have created in the script. However the logic remains the same. as of now My expression is

=count({<[extra inventory]={1}>} Distinct [TRANS_ID]) Where [extra inventory] is the condition I created in the script. and I have a straight table thats pulling records that satisfies the condition. but it is also pulling all values in expiration date field. how do I show only those that do not have an expiration date. I tried : =count({<[extra inventory]={1}, [expiration date]={'*'}>} Distinct [TRANS_ID]) and it brought those records that had expiration dates. how do I achieve the opposite of it, i.e. records that do not have an expiration date. I am hoping for a fix on the front end. either a set anlaysis expression combined with my main expression above or a calculated dimension for the [expiration date]. Thanks much

Re: How to include only those records that has a null Value

ramoncova06 — Sat, 18 Apr 2015 19:22:27 GMT

count({$<ProductID={"=nullcount([expiration date])=0"}, [extra inventory] = {1}>} distinct [TRANS_ID])

or on your current expression change it to [expiration date] -= {'*'}

Re: How to include only those records that has a null Value

anbu1984 — Mon, 20 Apr 2015 08:28:01 GMT

=count({<[extra inventory]={1}>} Distinct If(IsNull([expiration date]),[TRANS_ID]))