Count Occurrences in String
How can I find a string within a String?
My String looks like:
COPD|ARDS|Pleural Effusion
I am attempting to perform a count of occurrences like so:
SELECT SessionNumber,EventDate,
round(count( case when `Reg Category` = 'G' and `Interest Areas` LIKE '%Pleural Effusion%' then 1 end )/count(*) * 100,0) As In_Training_Member_Pleural_Effusion_,
round(count( case when `Reg Category` = 'G' and `Interest Areas` LIKE '%Non-Invasive Ventilation%' then 1 end )/count(*) * 100,0) As In_Training_Member_Non_Invasive_Ventilation_,
count(*) As session_total_count FROM private_merge GROUP BY EventDate,SessionNumber order by EventDate, SessionNumber, session_total_count;
I DO NOT FEEL COMFORMATABLE USING LIKE!
Many of my counts end up as zero, which is not correct.
I cannot seem to get CONTAINS to work.
Is that a viable function? Any suggestions?
I have a hard locating a list of functions for DOMO.
Best Answer
-
I think I found the issue.
When I print my values in Eclipse (IDE) I realized the Console is probably chopping my query. ( I generate the query using java in Eclipse).
I wasnt getting the entire query when I copy-paste, I think that is issue.
Therefor, I was missing records in the results count.
0
Answers
-
This was a bit of a learning experience for me, so thank you for asking the question.
There is a REGEX() expression in MySQL, but that does not appear to be available in the version that DOMO is using. However, if you are willing to change your data flow to a redshift data flow you can accomplish something like this:
SELECT
*
,round(count(CASE WHEN "Reg Category"='G' and "interest areas" ~ 'Pleural Effusion' then 1 end) / count(*)*100,0) AS "In_Training_Member_Pleural_Effusion_"
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Another reason that you may be getting zeros is that you are using an aggregate function Count().
This means that you need to use group by at the end of your select statement or the MySQL dataflow will simply look at the first row of data that it encounters
EDIT
**sorry, just read the code from your original post and it looks like you did use a group by clause**
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
I think I found the issue.
When I print my values in Eclipse (IDE) I realized the Console is probably chopping my query. ( I generate the query using java in Eclipse).
I wasnt getting the entire query when I copy-paste, I think that is issue.
Therefor, I was missing records in the results count.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive