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
Solved! Go to 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 ;;
}
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