Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Sorting nulls last

  • 15 November 2019
  • 16 replies
  • 1288 views
madziekk

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

View original

16 replies

P

I am looking for the same solution…

C
Userlevel 1

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})

madziekk

@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.

fabio
Rank
Userlevel 7
Badge

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

fabio
Rank
Userlevel 7
Badge

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

Prasham_Ashesh

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

fabio
Rank
Userlevel 7
Badge

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 ;;

}
Prasham_Ashesh

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

fabio
Rank
Userlevel 7
Badge

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

Aleksandr K.

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.

lokosmkd

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. 

adamplatten93

Hey, I have come across this problem. I want to keep the nulls in the data but them to be sorted last. Is this possible without converting the nulls to a different value?

Dmitri S

Hello @fabio - are you aware where Looker about control null sort order?

In our case we have to control it for 50+ measures, so I’m currently looking for some dynamic liquid...

fabio
Rank
Userlevel 7
Badge

Hello @fabio - are you aware where Looker about control null sort order?

In our case we have to control it for 50+ measures, so I’m currently looking for some dynamic liquid...

I don’t know of any way to cause the order_by field to dynamically reflect the field that uses it. The best I can think of is setting up a linter rule to check to make sure the explicitly defined order_by fields are correctly paired.

Dmitri S

@fabio thank you for the response and suggestion about Linter! And it seems there is no direct way to handle it by Looker yet. 

Considering that we need it for API requests and it’s possible to know which field is sorted the solution can be also using parameter + dynamic measure:

view: fov {

  parameter: field_to_sort {
    type: unquoted
    hidden: yes
    allowed_value: {value: "table_1.msr_1"}
    allowed_value: {value: "table_1.msr_2"}
    ...
    allowed_value: {value: "table_1.msr_K"}
    allowed_value: {value: "table_2.msr_1"}
    allowed_value: {value: "table_2.msr_2"}
    ...
    allowed_value: {value: "table_L.msr_N"}
  }

  measure: nulls_last_dynamic_msr{
    type: number
    hidden: yes
    sql: IFNULL("{% parameter field_to_sort %}",-1) ;;
  }
}

 

fabio
Rank
Userlevel 7
Badge

Considering that we need it for API requests and it’s possible to know which field is sorted the solution can be also using parameter + dynamic measure:

Ah, yes, if your use case is from the API and therefore it’s not too much burden to specify something twice in the request, then you can definitely make a single measure that could sort differently depending on the parameter value. What you’ve proposed above looks like a good idea

Reply


Terms of Service