Derived Columns
Derived Columns Logical Functions
Derived Columns
Derived Columns Logical Functions
Utilize logical functions to apply conditional logic, handle null values, and manage errors in your derived columns. These functions, such as CASE, IF, and COALESCE, enable dynamic data categorization, error handling, and flexible data transformation, allowing you to tailor data outputs based on specific conditions and scenarios.
CASE (Conditional Logic)
- Syntax:
case(when(yesno_arg, value_if_yes), ...) - About: Executes a series of conditions and returns a corresponding value for the first true condition.
- Example: To categorize customers based on MRR,
case(when(MRR > 1000, "Enterprise"), when(MRR > 500, "Mid-market"), "SMB")labels customers as “Enterprise” or “Mid-market” based on their MRR, or “SMB” by default.
COALESCE (First Non-Null Value)
- Syntax:
coalesce(value_1, value_2, ...) - About: Returns the first non-null value from a list of columns.
- Example: To ensure a display name is always shown,
coalesce(first_name, username)would usefirst_nameif it’s not null, otherwiseusername.
IF (Conditional Statement)
- Syntax:
if(yesno_expression, value_if_yes, value_if_no) - About: Evaluates an expression and returns one value if true, and another if false.
- Example: To check if a subscription is active,
if(status = "Active", "Yes", "No")returns “Yes” if the status is active, “No” otherwise.
IS_NULL (Null Check)
- Syntax:
is_null(value) - About: Tests if a value is null.
- Example: To verify if a customer’s last payment date is recorded,
is_null(last_payment_at)would return Yes if it’s null, indicating a missing payment date.
IFERROR (Error Handling)
- Syntax:
iferror(value, value_if_error) - About: Returns the value unless it’s an error, in which case it returns an alternative.
- Example: When calculating an average,
iferror(total_payments / number_of_payments, 0)would prevent division by zero errors by returning 0 ifnumber_of_paymentsis zero.
IFNA (Handle #N/A Errors)
- Syntax:
ifna(value, value_if_na) - About: Evaluates a value and returns it unless it’s an #N/A error, in which case it returns an alternative.
- Example: To handle missing MRR values,
ifna(MRR, 0)could replace #N/A with 0 in revenue calculations.
IFS (Multiple Conditions)
- Syntax:
ifs(condition1, value1, condition2, value2, ...) - About: Tests multiple conditions and returns the corresponding value for the first true condition.
- Example: To classify subscriptions by tenure,
ifs(diff_months(activated_at, today()) > 12, "Yearly", diff_months(activated_at, today()) > 1, "Monthly", "New")categorizes them as “Yearly,” “Monthly,” or “New” based on how long ago they were activated.
SWITCH (Match Cases)
- Syntax:
switch(expression, case1, value1, [default or case2, value2, ...]) - About: Compares an expression against a list of cases and returns the corresponding value for the first match.
- Example: To translate a subscription status code to a text status,
switch(status_code, 1, "Active", 2, "Paused", 3, "Cancelled", "Unknown")switch(status_code, 1, "Active", 2, "Paused", 3, "Cancelled", "Unknown")would return “Active,” “Paused,” or “Cancelled” based on the code, or “Unknown” if there’s no match.
Was this page helpful?