Confusion - LOWER

ArborRose

I have formula logic to try to standardize insurance company names. I use a series of case conditions to try to create a new field with standard naming. But my logic doesn't seem to work the way it would in SQL.

My formula contains searches such as…

CASE
WHEN LOWER(insurance_company_name) LIKE '%aetna%' AND LOWER(insurance_company_name) LIKE '%medical%' THEN 'Aetna - Medical'
WHEN LOWER(insurance_company_name) LIKE '%aetna%' AND LOWER(insurance_company_name) NOT LIKE '%medical%' THEN 'Aetna - Dental'
END

It's my belief the statement above should find the word aetna with case insensitive results. Meaning all the following would be translated.

Can someone explain why Domo does not know the word 'aetna' exists in these strings? Does it have anything to do with being at the front of the string and my search for '%aetna%' is suggesting there may be characters before the occurrence?

ColemenWilson

I replicated your data and beastmode and I am getting the expected results:

Could you try creating a table card as I have done with your original column and then your beastmode column next to it? What results are you seeing?

GrantSmith

Alternatively instead of LOWER() you can just use ILIKE which is the case-insensitive version if LIKE.

The % at the beginning will match 0, 1 or multiple characters so it will still match aetna at the beginning.

If you had just aetna% then it'd only match the value at the start of the string.

But your beast mode looks correct. What values is your beast mode returning that causes it to be incorrect?

MarkSnodgrass

I have used the LIKE in similar ways that you are, so it is odd, but here are some additional suggestions.

Pre-cleaning functions

TRIM()

SQUASH_WHITESPACE()

Alternative function

Use STR_CONTAINS instead. It will return a 1 or 0 that you can then act off of. Example

STR_CONTAINS(LOWER(name),'aetna')

marcel_luthi

I guess you're doing this in ETL, can you debug and see if before the tile where COALESCE is being applied, the tile where the CASE function is being applied is actually returning any values? If so, as you're not doing multiple, can you try replacing COALESCE for IFNULL, a bit less flexible but a function that has been around for longer than COALESCE.

ColemenWilson

I replicated your data and beastmode and I am getting the expected results:

Could you try creating a table card as I have done with your original column and then your beastmode column next to it? What results are you seeing?

GrantSmith

Alternatively instead of LOWER() you can just use ILIKE which is the case-insensitive version if LIKE.

The % at the beginning will match 0, 1 or multiple characters so it will still match aetna at the beginning.

If you had just aetna% then it'd only match the value at the start of the string.

But your beast mode looks correct. What values is your beast mode returning that causes it to be incorrect?

ArborRose

https://community-forums.domo.com/main/discussion/comment/79387#Comment_79387

It's simply not finding the occurrence. It should see that those strings contain "aetna" in whatever case and then give the appropriate new field value "Aetna - Dental".

MarkSnodgrass

How about if you reworked it to be a nested case statement like this

CASE WHEN LOWER(insurance_company_name) LIKE '%aetna%' THEN    
  CASE WHEN LOWER(insurance_company_name) LIKE '%medcial%' THEN 'Aetna - Medical'    
  ELSE 'Aetna - Dental'    
  END
ELSE insurance_company_name
END

ColemenWilson

I'm wondering if there is some other filter aggregation being used on your card that may be causing no results to be shown. Are you using this formula in an ETL or in a beastmode on a card?

ArborRose

https://community-forums.domo.com/main/discussion/comment/79378#Comment_79378

There's nothing confidential in this, so I'll show a clip of my actual results. After the formula happens, if no condition is met, the field is populated with a string of X's.

The formula that overwrites the X's…

ArborRose

@ColemenWilson - it appears as a formula tile near the end of an ETL. I have diverted a debug output so I can see the values immediately after the tile executes. I've also taken a few steps of the condition to a test ETL and I get the same results. It simple doesn't see the substring in there.

ArborRose

I have not tried nesting, Mark. I did try simplifying the criteria. I ran one that was just something simple like…

CASE WHEN LOWER(insurance_company_name) LIKE '%aetna%' THEN 'Aetna' END

Same thing..it couldn't find it. It's all good feedback, thank you. The fact that Colemen says his comes out correctly, and y'all seem to think the LOWER should be working as I do….I'm now thinking my data may have some extended characters or something in it. Maybe there's a character in there that isn't visible to me.

The source of the data comes to me via an api connection. I'm going to create a webform with a few lines of sample data to use with the same logic. This will reproduce what Colemen mentioned to verify whether my logic should actually work if the data is ascii and not ansi (doesn't contain extended non-visible characters).

@grant - I was not aware of an ILIKE statement. I am going to try all these suggestions. Thank you.

MarkSnodgrass

I have used the LIKE in similar ways that you are, so it is odd, but here are some additional suggestions.

Pre-cleaning functions

TRIM()

SQUASH_WHITESPACE()

Alternative function

Use STR_CONTAINS instead. It will return a 1 or 0 that you can then act off of. Example

STR_CONTAINS(LOWER(name),'aetna')

marcel_luthi

I guess you're doing this in ETL, can you debug and see if before the tile where COALESCE is being applied, the tile where the CASE function is being applied is actually returning any values? If so, as you're not doing multiple, can you try replacing COALESCE for IFNULL, a bit less flexible but a function that has been around for longer than COALESCE.