Use default value of a field

[paco_lopez_toqi]

hi, i have this measure that depends on the field view_limit_quantity_daily is defined or not.

If the field is defined i get the field. But if the field is null I need to get a default value stored in the DB in other table without relation with my table.

The default value and the field are numbers

here the measure:
  measure: customer_viewLimitQuantityDaily{
    type:  sum
    sql: CASE
    WHEN ${view_limit_quantity_daily} is null THEN ?????
    ELSE ${view_limit_quantity_daily}
    END ;;
  }

What i must do? what i have to put in the ????? place?

Thanks

BR

[JMJ1 · Accepted solution]

Hola Paco,

If the default value stored in the DB  as a single value of a table, you should to define the table as a lookml view and include it in the model as a cross join against the fact table , for example :

    join: table_x{
      type: cross

       sql_on: 1 = 1 ;; # this sql_on condition is required in some dialects,
    }                             # removing if you experience problems with cross joins

then you should write:

 measure: customer_viewLimitQuantityDaily{
    type:  sum
    sql: CASE
    WHEN ${view_limit_quantity_daily} is null THEN ${table_x.default_value}
    ELSE ${view_limit_quantity_daily}
    END ;;
  }

[paco_lopez_toqi]

Thanks JMJ, i will try and let you know

Regards