Confusion - LOWER
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?
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **
Best Answers
-
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?
If I solved your problem, please select "yes" above
0 -
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?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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')**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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.
0
Answers
-
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?
If I solved your problem, please select "yes" above
0 -
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?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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".
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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?
If I solved your problem, please select "yes" above
0 -
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…
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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')**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 681 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 9 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive