Question

[Solved] Missing values for 'dimension' were not filled. It is unclear what values to fill for this field

  • 21 December 2017
  • 7 replies
  • 5019 views

So I have these lines that create cohorts:


dimension: loan_purpose2 {

label: “Purpose”

sql: CASE WHEN ${TABLE}.loan_prps != 03

THEN ‘Purchase’

WHEN ${TABLE}.loan_prps = 03 and ${TABLE}.cash_out_refi_ind = ‘Y’

THEN ‘Cash-Out Refinance’

WHEN (${TABLE}.loan_prps = 03 and ${TABLE}.cash_out_refi_ind = ‘N’) OR (${TABLE}.loan_prps = 03 and ${TABLE}.cash_out_refi_ind IS NULL)

THEN ‘No Cash-Out Refinance’

ELSE NULL

END ;;

}


And when I run it in a query it results in the Warning:


Missing values for ‘mortgage_data_warehouse.loan_purpose2’ were not filled. It is unclear what values to fill for this field.


And it doesn’t show the ‘Unknown’ bucket. Why is it not picking up the unknown?


Thank you!


Solved


7 replies

Userlevel 3

Hey @jimRL,


Looks like you were able to solve the issue; glad you figured it out! Could you share what was causing the “missing values” warning message to appear and how you were able to resolve it? It could help other customers who encounter this message know what to look for.


Best,

Chris

I’m having an identical problem while using SQL: Case. When I display the column there appears to be no missing rows so I’m not sure what this error message means.


Any idea how this was solved?

Userlevel 3

Hey @quinnftw,


Generally, this error occurs when dimension fill is being applied to a dimension that cannot use dimension fill. Was your dimension previously defined as a yesno, tier, date, or case type dimension? If it was previously defined as one of these types but was later changed to a string or number type, you might encounter this error. You can read more about dimension fill here: https://help.looker.com/hc/en-us/articles/360001288488-How-to-Fill-In-Missing-Values


If dimension fill is turned on, you can turn it off by selecting Remove Filled in Values from the gear menu for that column, which should remove the error message. If not, please send the details of your explore, including screenshots and LookML definitions, to help.looker.com, and we can take a look!

This solved my issue thanks!

For those of you looking for the solution: it can be found at the following documentation. You need to allow_fill: no. https://docs.looker.com/reference/field-params/allow_fill

@chris.seymour’s previous comment is helpful… what’s particularly noteable in that documentation (and not immediately obvious to anyone trying out this feature) is that filling is only allowed under very specific circumstances:

Dimension fill is available for dimensions with yes/no values, tiered values, and most date types. It can also be applied to any dimension based on a list of values, via the case or tier parameters.

Dimension fill will turn on automatically for queries that run with a single dimension and/or a single pivot, just as long as you haven’t applied filters to any measures.

There are a few cases when you will not be able to dimension fill:

  • Dimensions that have a filter applied to them and also have a fixed number of values, such as yes/no, days of the week, days of the month, etc.
  • Drilling into a pivoted dimension.
  • Dimensions where your Looker developer has used the order_by_field parameter or disabled the allow_fill parameter.

 

You might be able to skirt some of these restrictions by returning dimension values via measures, applying further processing via calculations are results are returned, etc. , but expect a fair amount of legwork in doing so, and a fair amount of hair-pulling as it inevitably complains that you’re not following looker’s arcane rules :P

 

 

Userlevel 7
Badge +1

I have a bit of a different scenario. I forced allow_fill:no because I don’t need it but yeat I am getting the following error: 

 

Missing dates for 'field' were not filled. It is unclear what values to fill for this field.

 

Oh, I think I just needed to refresh the whole page so that Looker loads the field with this parameter

Reply