Magic ETL

Magic ETL

Count Occurrences in String

How can I find a string within a String?

My String looks like:

  1. COPD|ARDS|Pleural Effusion

I am attempting to perform a count of occurrences like so:

  1. 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.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

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

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

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

     

     

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In