topic Sort Table On Column With Null Dates in QlikView

Sort Table On Column With Null Dates

rebelfox — Thu, 06 Aug 2015 12:35:00 GMT

I have a table with a date field loaded from three legacy number fields using makedate(YYYY,MM,DD).

In some cases there is no date so the derived date is null.

This is Ok but when I sort on the table by clicking on the column header to list either the higest date or the lowest date at the top of the table

rows, Null ndates do not show up at the top of the table when the lowest dates are at the top of the table. Instead it only shows the lowest

real dates and the nulls still appear at the bottom of the table.

Is there a way of making the nulls appear at the top of the table when the table is sorted on the date?

Re: Sort Table On Column With Null Dates

pokassov — Thu, 06 Aug 2015 12:50:40 GMT

Hello!

You can use this approach:

change your field Date on :

if(isnull(Date), 'null', Date)

Re: Sort Table On Column With Null Dates

rebelfox — Thu, 06 Aug 2015 15:12:34 GMT

Gosh of course!.

Thanks.

Re: Sort Table On Column With Null Dates

rebelfox — Thu, 06 Aug 2015 15:40:54 GMT

Hmm hasn't quite worked.

The date shows 'null' for a bad date but the 'good' dates are now formatted differently.

Instead of '15/01/2015' for example I get '42022'.

On my load statement I have:-

    BRE36 As [Invoice-Settled-Month],
     BRE37 As [Invoice-Settled-Day],
     if(BRE35 = 0, 'NoDate', MakeDate(BRE35,BRE36,BRE37) ) As [Settlement-Date],
     MakeDate(BRE35,BRE36,BRE37) As [old-Settlement-Date],
     BRE38,

Re: Sort Table On Column With Null Dates

pokassov — Fri, 07 Aug 2015 06:45:11 GMT

Hi!

if(isnull(Date), 'null', Date(Date))