Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

phongnnguyen
New Contributor II

Max without NULL

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

AccountClientClosedate
A1A10-20-2017
A2A10-19-2017
A1A-
A2A-

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

7 Replies

Re: Max without NULL

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

phongnnguyen
New Contributor II

Re: Max without NULL

Hi Sunny Talwar,

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

sunilkumarqv
Valued Contributor II

Re: Max without NULL

hi ,

what is you dimensions and expression ,

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

MVP
MVP

Re: Max without NULL

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

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

qlikviewwizard
Honored Contributor II

Re: Max without NULL

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,

];

Capture.PNG

MVP
MVP

Re: Max without NULL

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ChennaiahNallani
Contributor III

Re: Max without NULL

Remove null values in the script only

Like below exp and try.

SET NullValue = 'Null';

NULLASVALUE *;