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'
.