Type yesno returns null values as no

We had been using yesno as an equivalent to boolean, which has thrown off our reporting.
For example, we have a boolean dimension passed_test?, where there are many rows that do not have the input to this test. If we look at the percent of test failing using type yesno, it will give us
(# test failed + # did not take test)/
(#test passed + # test failed + # did not take test)

By treating the null values as no, it throws off the ratio, and we can’t see easily the number of people who have taken the test. We can simply not specify a type to get the required functionality, but then we lose the benefits of a yesno dimension (having the right predefined options on filters for example)

How have people who want to keep null values in their boolean data deal with this? Any help or direction would be appreciated.

3 14 8,417
14 REPLIES 14

Can I see the LookML for how the yesno dimension is currently set up? Ideally would null rows just be ignored completely or would they return yes?

Ideally they would return as null. We would like the ability to pivot by this dimension and see results for True, False, and Null.

The lookml is merely defining a boolean column from a sql table as a yesno dimension.

I see okay, it is currently not possible to have nulls return as their own type with a yesno dimension but I have passed along the request to our product team. In the meantime what you could do is make a sql case dimension that carries out this functionality. So something like:

Old LookML ``` - dimension: took_test sql_case: 'Yes': ${TABLE}.boolean_field = True 'No': ${TABLE}.boolean_field = False 'Null': ${TABLE}.boolean_field = NULL ```
New LookML ``` dimension: took_test { case: { when: { sql: ${TABLE}.boolean_field = True ;; label: "Yes" }
when: {
  sql: ${TABLE}.boolean_field = False ;;
  label: "No"
}

when: {
  sql: ${TABLE}.boolean_field = NULL ;;
  label: "Null"
}

}
}

</details>

a big +1 to this. we’re running into this exact issue as well.

@john_handshake, @sampeterson, and anyone else running into this,

At Looker, we made a deliberate decision very early on to avoid the 3VL problem(EVIL FUZZY LOGIC) with NULLs and asymmetry of results that occur for inverse logical conditions on Booleans. We COALESCE NULLs and automatically treat them as False in order to stay in the simplified 2VL world to avoid confusion for end users(and the assumption that reports were false or inconsistent).

If you need to represent NULLs please use the example @ian2 demonstrated with strings rather than real NULLs, this guarantees consistency and accuracy of results across different databases and for inverse conditions under the same logical operation.

We will probably never support the pure 3VL world with the yesno type because there’s so much code written around it already, it is not a problem that can be solved without creating disparity and confusion amongst most use cases unless the users have extensive knowledge and awareness of relational algebra. I would say that 90-95% of users will not have awareness of what is actually occurring and assume that the report is inaccurate due to 3VL behavior.

We may support a Boolean type one day with the full 3VL behavior in the future, we definitely want to play around with it more and try to figure out how to release it without as much headache that goes with the nuances.

amcgrath83
Participant II

There’s a typo in this example.

should be:

Old LookML
- dimension: took_test
  sql_case: 
    'Yes': ${TABLE}.boolean_field = 'true'
    'No': ${TABLE}.boolean_field = 'false'
    'Null': ${TABLE}.boolean_field = NULL
New LookML ``` dimension: took_test { case: { when: { sql: ${TABLE}.boolean_field = 'true' ;; label: "Yes" }
when: {
  sql: ${TABLE}.boolean_field = 'false' ;;
  label: "No"
}

when: {
  sql: ${TABLE}.boolean_field = NULL ;;
  label: "Null"
}

}
}

</details>

Hey Alex, if you are using a sql_case parameter on a boolean field, you won’t want to use single quotes. Treating the boolean fields as a string will not give the logic we want as it is comparing it to a string rather than a boolean field. But you will need single quotes if you are using string fields. For example:

Old LookML

  - dimension: category_case_test
    sql_case:
      category1: ${category} = 'advertising'
      category2: ${category} = 'analytics'
      category3: ${category} = 'automotive' 

New LookML ``` dimension: category_case_test { case: { when: { sql: ${category} = 'advertising' ;; label: "category1" }
when: {
  sql: ${category} = 'analytics' ;;
  label: "category2"
}

when: {
  sql: ${category} = 'automotive' ;;
  label: "category3"
}

}
}

</details>

For each old LookML code block in this article and its comments, I just added the New LookML equivalent code.

Also having a similar problem. I’m interested in how you guys will engage this in the future.

If I am not mistaken:
sql: ${TABLE}.boolean_field = NULL ;;
should be:
sql: ${TABLE}.boolean_field IS NULL ;;

Hey @Sebastiaan1,

Yep you’re correct. When performing comparisons with null values, the IS and IS NOT keywords should be used.

I.e.:
82d90bd32bf70b82916279f6b9fbed7091f6d39f.pnga348048c65c69a648389736b0a23db755fa5a583.png

Thanks,
Philip M.

It’s also worth saying that if you’re ok with the nulls being included, but you simply want them to be treated as true (rather than the default of false), that’s easily achieved.

dimension: null_means_true {
    type: yesno
    sql: COALESCE(${TABLE}.null_means_true, true) ;;
  }

Dawid
Participant V

I think this would be ideal if we could control it on a field level. Sometimes you want NULLs if there’s a meaning to the NULL.

And it takes away the need for you to make a decision. You give us a default plus ability to change it if we need it and the configurability of such little things is what makes tech products great : )

@Looker 
It has been a few years, is there already a working solution to be able to show a NULL value for a yesno dimension, since this has a different meaning than TRUE or FALSE ?

kuopaz
Participant IV

I’ve had a related issue.

# test example, any logic that returns null - as No

  dimension: test_bad_yesno {
    type: yesno
    sql: 1 = null ;;
  }

  dimension: test_bad_not {
    type: yesno
    sql:  not (${test_bad_yesno}) ;;
  }

The NOTing does not switch No to Yes for null values because NOT (NULL) returns NULL in SQL.

Workaround below, converting the first yesno to strings.

  dimension: test_good_yesno {
    type: string
    sql:
      case
        when 1 = null then 'Yes'
        else 'No'
      end
      ;;
  }

  dimension: test_good_not {
    type: yesno
    sql: ${test_good_yesno}  = 'No';;
  }
Top Labels in this Space
Top Solution Authors