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: 
Anonymous
Not applicable

Expression to Sort Dates - blanks at top

I have a date field in a table. The requirement is for the records that have no dates to appear at the top of the list. Sorting by Ascending or Descending leaves the blank dates at the bottom. Is there a Sort expression I can use? I can think of other work arounds but a sort expression would be a neater solution.

10 Replies
Bill_Britt
Former Employee
Former Employee

Have you tried Orderby, on the date field?

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
Anonymous
Not applicable
Author

Thanks but Interbase only alows Asc & Desc in SQL which I can already do within the QV 'Sort by' expression combo box. I think MySQL may allow it. Ideally I would like an expression that will place Null values at the top of a list of dates.

prieper
Master II
Master II

When retrieving from ODBC-source or a .txt-file would suggest to use the NullDisplay-functionality

<h4>SET NULLDISPLAY=<sym>;</h4>
The symbol <sym> will substitute all NULL values from the ODBC data source on the lowest level of data input. <sym> may be any string.


Use #N/A as symbol and it will be sorted as first/last and can be selected

HTH
Peter



Anonymous
Not applicable
Author

Interbase doesn't have NULLDISPLAY in it's SQL language.

Had to resort to using an If statement on a pre load and sorting by Numeric Value. Would have thought there would be a solution by using the 'Sort by' expression in the table properties but no one has come forward.

If



(IsNull(DUE_DATE), 'Nil',date(DUE_DATE,'DD/MM/YY')) as DueDate,

prieper
Master II
Master II

When you intend to use the script on 64-bit-server, you should test the expression not with IsNull, but check on the length of the string:

LEN(Mydate) = 0 ....

HTH
Peter

Anonymous
Not applicable
Author

Thanks Peter

We're not on 64 bit servers yet but useful to know.

Dave

Anonymous
Not applicable
Author

Wrong post. Error in browser

Anonymous
Not applicable
Author

Wrong post. Error in browser

Anonymous
Not applicable
Author

Have you tried an Expression in sorting?

Try this expression:

Normal 0 false false false LV X-NONE X-NONE MicrosoftInternetExplorer4 =If(Len(Trim(DUE_DATE))=0,1)

It helds empty values in DUE_DATE field as 1.
Usin Ascending sort empty dates will be on top

If you want to use Descending sort, then change 1 to 99999999, then empty values also will be on top