Why Doesn't Coalesce work in an If/Case Statement?

[katzr]

I am trying to write a search that if the field= Email then perform a coalese, but if the field isn't Email- just put in the field- below is what I have written. It seems like coalesce doesn't work in if or case statements. I also tried to accomplishing this with isNull and it also failed.

| eval $fieldToMatch$= if($fieldToMatch$==Email, coalesce(Email,NotifyAddress), $fieldToMatch$)

Thanks for the help!

(essentially i am doing a join on a field and the field could either be email, an ID, etc. but if the field to match on is email- the field NotifyAddress might also contain some emails)

[somesoni2 · Accepted solution]

Give this a try

| eval "$fieldToMatch$"= if("$fieldToMatch$"=="Email", coalesce(Email,NotifyAddress), '$fieldToMatch$')

[katzr]

yes this worked thank you! But can you explain to me what the difference between " and ' is and when to use them?

Because the goal of my search is to match two data sources on a field (Email for example). The one data source is where I was using this functionality above with Email and Notify address, but the other data source- the field to match on may also be named Email so whoever wrote the search referred to that field as "Email".

Essentially I need to understand why you use " vs. ' vs. just the field name to make sure this doesn't mess up the rest of my search. Thanks for the help!

[somesoni2]

If the field names contains special characters, you would enclose them in single quotes in eval/where expressions (e.g. ..| where <<expression>> or ..|eval fieldname=<<expression>>). For eval, you can use double quotes on the left side of = sign (first one after field name), and must use single quotes on right side of it.

[katzr]

okay that makes sense- if I am pulling from two different sources who have fields with the same name- how do I distinguish them?

Cause the Email used in the above function should all be from one source, but the other source used also has the field Email in it.

[somesoni2]

There is no way to differentiate just based on field name as fieldnames can be same between different sources. If your expression/logic needs to be different for different sources (though applied on same field name), then you'd need to include source identifier field (field/fields that can uniquely identify source) into your expressions/logic. e.g. For same original problem, with different expression for sourceA and sourceB (assuming field source is uniq identifier), so set the value of field Email differently, you can do like this:

| eval "$fieldToMatch$"= case("$fieldToMatch$"=="Email" AND source="sourceA", coalesce(Email,NotifyAddress), "$fieldToMatch$"=="Email" AND source="sourceB", coalesce(Email,Receipiers), , 1=1, '$fieldToMatch$')

[cmerriman]

why not try something like this:

| eval $fieldToMatch$= case($fieldToMatch$==Email AND isnotnull(Email), Email, $fieldToMatch$==Email AND isnull(Email),NotifyAddress, 1=1,$fieldToMatch$)

[richgalloway]

I think you might be confused about what coalesce does. It selects the field that is not null. If both the Email and NotifyAddress fields might contain emails then you probably want to merge them using Email.NotifyAddress.

[katzr]

No I want to use the functionality of coalesce- so if Email is null- then pull in the value from Notify Address. I don't care about Notify Address if Email has a value. Do you know why this isn't working with if/case?

[somesoni2]

What is $fieldToMatch$ here? a token from some form input?

[katzr]

yes it is a token that could either be the field Email, ID,Name. So if the field to match on is Email- I need to check for any additional emails to match on that could be in the field NotifyAddress.