Use default value of a field

Looker Q&A Looker
1

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

2

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

1 Like
3

Thanks JMJ, i will try and let you know

Regards

1 Like