Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
phongnnguyen
Contributor II
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
sunny_talwar

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

phongnnguyen
Contributor II
Contributor II
Author

Hi Sunny Talwar,

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

sunilkumarqv
Specialist II
Specialist II

hi ,

what is you dimensions and expression ,

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

tresesco
MVP
MVP

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

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

qlikviewwizard
Master II
Master II

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III

Remove null values in the script only

Like below exp and try.

SET NullValue = 'Null';

NULLASVALUE *;