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