Count Occurrences in String

mc1392
mc1392 Member
in Magic ETL

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

  • mc1392
    mc1392 Member
    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.

     

     

Answers

  • ST_Superman
    ST_Superman Domo Employee

     

    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_"

  • ST_Superman
    ST_Superman Domo Employee

    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**

  • mc1392
    mc1392 Member
    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.

     

     

Categories