Solved

Use default value of a field

  • 18 January 2022
  • 2 replies
  • 60 views

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

icon

Best answer by JMJ 18 January 2022, 17:54

View original

This topic has been closed for comments

2 replies

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

 

Thanks JMJ, i will try and let you know

Regards