The COALESCE function returns the first non-NA expression in a list of expressions, or NA when all of the expressions evaluate to NA.
Example 7-45 Using COALESCE to Determine the Sales Price of a Product
Assume that you have defined the following objects in your analytic workspace. (Note that the sale formula uses the COALESCE function for its calculations.)
DEFINE product_id DIMENSION TEXT DEFINE supplier_id DIMENSION TEXT DEFINE list_price VARIABLE DECIMAL <product_id supplier_id> DEFINE min_price VARIABLE DECIMAL <product_id supplier_id> DEFINE sale FORMULA DECIMAL <Product_id supplier_id> EQ COALESCE(0.9*list_price, min_price, 5)
The following code illustrates limiting supplier_id to a single value and displaying a report that shows the list price, minimum price, and sale price for the products provided by that supplier.
LIMIT supplier_id TO '102050'
REPORT DOWN product_id list_price min_price sale
----------SUPPLIER_ID-----------
-------------102050-------------
PRODUCT_ID LIST_PRICE MIN_PRICE SALE
-------------- ---------- ---------- ----------
2382 850.00 731.00 765.00
3355 NA NA 5.00
1770 NA 73.00 73.00
2378 305.00 247.00 274.50
1769 48.00 NA 43.20
1660 16.45 16.45 14.80