topic Expression to Sort Dates - blanks at top in QlikView

Expression to Sort Dates - blanks at top

Anonymous — Mon, 09 Nov 2009 05:05:40 GMT

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.

Expression to Sort Dates - blanks at top

Bill_Britt — Mon, 09 Nov 2009 09:26:47 GMT

Have you tried Orderby, on the date field?

Expression to Sort Dates - blanks at top

Anonymous — Mon, 09 Nov 2009 16:14:40 GMT

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.

Expression to Sort Dates - blanks at top

prieper — Mon, 09 Nov 2009 20:26:17 GMT

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

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 00:03:53 GMT

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.

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

Expression to Sort Dates - blanks at top

prieper — Wed, 11 Nov 2009 04:05:52 GMT

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

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 15:29:01 GMT

Thanks Peter

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

Dave

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 16:54:47 GMT

Wrong post. Error in browser

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 16:55:28 GMT

Wrong post. Error in browser

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 17:04:05 GMT

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

Expression to Sort Dates - blanks at top

Anonymous — Wed, 11 Nov 2009 17:07:42 GMT

uups, I had an error in browser, and now there are 3 posts of mine.