topic Re: Max without NULL in QlikView

Max without NULL

phongnnguyen — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys,

I have a problem with my set analysis. I want to show Max of Close_date without NULL. My data like table below

Account	Client	Closedate
A1	A	10-20-2017
A2	A	10-19-2017
A1	A	-
A2	A	-

Client A is main account and each Client has many child Account . Because this is certain way to store the data . So each Account has each Closedate. But I just wanna show 1 Closedate is 10-20-2017 ( because this is Close date of Main Account .

I use my expression like this: max({<closedate={'*'}>}closedate ). But it doesnt work.

Please give me solutions for this issue. Many Thanks

Re: Max without NULL

sunny_talwar — Thu, 02 Aug 2018 02:08:09 GMT

What happens when you use the above expression? Can you elaborate?

Re: Max without NULL

phongnnguyen — Thu, 02 Aug 2018 02:11:03 GMT

Hi Sunny Talwar,

Its result is - ( NULL ) not is my expectation.

Re: Max without NULL

sunilkumarqv — Thu, 02 Aug 2018 05:29:11 GMT

hi ,

what is you dimensions and expression ,

try it max({<closedate={'*'}-{' '}>}closedate ).

Re: Max without NULL

tresB — Thu, 02 Aug 2018 05:32:54 GMT

Perhaps your date field is a text field. Try like:

Date(Max(Date#(closedate, 'MM-DD-YYYY')), 'MM-DD-YYYY')

Re: Max without NULL

qlikviewwizard — Thu, 02 Aug 2018 05:56:50 GMT

Hi Phong Nguyen,

You can achieve your requirement like this.

Data:

LOAD Account,Client,DATE(DATE#(Closedate,'MM-DD-YYYY'),'DD-MMM-YYYY') AS Closedate INLINE [

Account,Client,Closedate

A1,A,10-20-2017

A2,A,10-19-2017

A1,A,

A2,A,

];

Re: Max without NULL

jonathandienst — Thu, 02 Aug 2018 06:57:17 GMT

If your document was created using the latest version (September 2017), then you will need to use double quotes*:

Max({<closedate = {"*"}>} closedate)

Although Max() will ignore nulls any way, so the set expression is not required to exclude nulls.

*The rule has always been that single quotes is for a LITERAL, and double quotes for a SEARCH. This was ignored in earlier versions, but will be enforced for the latest and subsequent versions. See the release notes for more detail.

Re: Max without NULL

ChennaiahNallani — Thu, 02 Aug 2018 07:14:11 GMT

Remove null values in the script only

Like below exp and try.

SET NullValue = 'Null';

NULLASVALUE *;