Solved

Sorting nulls last

  • 15 November 2019
  • 11 replies
  • 1728 views

Hi. Is there a way to sort a column in a table in descending order with nulls last instead of first? The default behavior (nulls appear at the top) is most painful in merged results where a coalesce on LookML level is not possible. I can imagine that in a vast majority of cases nulls actually mean 0 and this seems like such a basic functionality that I wonder if I am not missing some obvious solution…

icon

Best answer by fabio 15 May 2020, 20:40

Hi @Prasham_Ashesh



I actually haven’t gotten to try it yet… but my first stab at it would be something like:



measure: foo {

...

order_by_field: foo_nulls_last

}



measure: foo_nulls_last {

type: <depends on type of foo, either number or string>

sql: CASE WHEN ${foo} is NULL THEN <value that sorts last> ELSE ${foo} END ;;

}

View original

11 replies

I am looking for the same solution…

I’ve always just added a table calculation that refers to the column you are referencing to and just do an if statement if that is null then result in 0. That way I can sort that new table calc and hide it, which will sort your results and can let the nulls be last.


if(is_null(${measure}),0,${measure})

@chy353 true, but this doesn’t work if you only display limited results (like top 500) because then you cannot sort by a calculation. Also, this is very annoying if you have many metrics in a report and have to create a set of calculations just for sorting purposes.

To my mind there should be an setting on metric / dimension level in LookML that lets set sort nulls first / last.

Userlevel 6

Hi Magda 🙂


There is a enhancement that we’re working on right now which should help as a workaround - allowing you to declare order_by_field on measures! Once it is released in the near future, you’ll be able to use it to affect null sort order.


Of course, it is still a workaround, and the ability to more explicitly control null sort order is still on our radar

Userlevel 6

order_by_field on measures will be in the 7.4 realease coming this month!

How would we do Order By NULL LAST using order_by_fields? I can’t use Table Calculations, as in the Table I have more than 5000 rows

@fabio

Userlevel 6

Hi @Prasham_Ashesh


I actually haven’t gotten to try it yet… but my first stab at it would be something like:


measure: foo {
...
order_by_field: foo_nulls_last
}

measure: foo_nulls_last {
type: <depends on type of foo, either number or string>
sql: CASE WHEN ${foo} is NULL THEN <value that sorts last> ELSE ${foo} END ;;
}

Thanks a lot @fabio I implemented the solution as suggested, but I had to give a large Constant value in <value that sorts last>, but I wanted to avoid that.


This Solution would work perfectly in normal cases but, I am calculating Days since Last Activity. If No Activity is Found I receive NULL otherwise, the corresponding difference between Current Date and Last Date of Activity , is Calculated.

I want that If I sort Days since Last Activity in Decreasing Order, NULLs should Appear first then others in Decreasing order and vice versa for Ascending Order, which I was able to achieve.


Though, In order to place NULLs last, for the above solution, I provided a large constant value in place of <value that sorts last> which was something I was trying to avoid. As at some point in Time, Days since Last Activity would most likely overtake the Constant value that I define here. It is okay for now because it would take years for that to happen, but still is there a way to avoid, giving a Large constant?


Thanks a lot for your help @fabio

Userlevel 6

Hi @Prasham_Ashesh - I don’t think so. We know it’s just a partial workaround, which is why we still have left open the broader request to be able to explicitly control null sort order

Hey! What are the constraints of implementing looking at null as the smallest value? Seems to be a very straight forward fix. Don’t think that this issue was reported just 11 months ago. I am using Looker for over 3 years now and it was always an issue.

I was really wondering about the same thing. We’ve been scraping Instagram followers for quite some time now and given that many of the attributes gathered are null because of the nature of the different profiles (private, public, with email, without, phone number etc) wanted to sort null as last.

 

So thanks for the questions, much appreciated. 

Reply