I have data that can be populated between 2 fields. One may be null and the other may be populated. I have this expression that looks at one of the fields, I need to incorporate another case when expression or possibly an if then else to accomplish this.
max(case when key_id = 'program.partner' then split(identifier,':')[6] end) as partner_id
I need to incorporate this with the above expression
max(case when key_id = 'partner' then identifier end)
I was thinking something like this, but it is not working
case when (max(case when key_id = 'program.partner' then split(identifier,':')[6]) is null then (max(case when key_id = 'partner' then identifier)) else '' end as partner_id,
Considering No Input data or expected output is given, In general, I suggest try COALESCE and CASE expressions. The COALESCE function returns the first non-null value in the list of arguments. Here's an example of how you can incorporate both conditions into a single statement:
COALESCE(
max(case when key_id = 'program.partner' then split(identifier, ':')[6] end),
max(case when key_id = 'partner' then identifier end)
) as partner_id
Explanation:
CASE Expressions: Each CASE expression checks for a specific key_id and extracts the desired value.
MAX Function: The MAX function is used to ensure that we get the maximum value from the results of the CASE expressions.
COALESCE Function: The COALESCE function returns the first non-null value from the list of arguments. If the result of the first CASE expression is NULL, it will return the result of the second CASE statement.
This way, if the value for key_id = 'program.partner' is NULL, it will fall back to the value for key_id = 'partner'.