12 Replies Latest reply on Mar 2, 2018 9:59 AM by Rakesh Cherukuri

    Help needed with RegExpr

    Rakesh Cherukuri

      Hi Tableau wizards,

       

      Need help in getting the dosage value for the below expression, any help would be appreciated,

       

      attached the sample data.

       

      Thanks for your time.

        • 1. Re: Help needed with RegExpr
          Andrew Bickert

          Hi Rakesh,

           

          Try the formula below, I added some IfNull statements so that you could iterate individually between the different types of extensions, since some of them (MillionUnits) also contains mL which was throwing off the calculations.

           

          "IFNULL(

          IFNULL(

          IFNULL(

          REGEXP_EXTRACT([Dose],'([\.,\d]+(mg)*\d(mg)*)') + REGEXP_EXTRACT([Dose],'(( mg))')

          ,REGEXP_EXTRACT([Dose],'([\.,\d]+( MillionUnits)( MillionUnits)*)'))

          ,REGEXP_EXTRACT([Dose],'(\d ?(mL))'))

          ,[Dose])"

           

          Also, there is a great tool out there called RegExr: Learn, Build, & Test RegEx  which allows you to see the different expressions and how to define them. One other Reference I would point you too would be Mark Jackson's blog, he has a very similar spreadsheet that he does for medical data: Tableau Zen: #Tableau 9.0 and Regular Expressions

           

          Kind Regards,

          Andrew

          • 2. Re: Help needed with RegExpr
            Rakesh Cherukuri

            Will try and let you know Andrew, Thanks

            • 3. Re: Help needed with RegExpr
              Rakesh Cherukuri

              It is working great for everything, but if I have only one digit in the first line then it is bringing lot of other stuff.

              I am trying to figure it out, but if you have any quick solution that would be greatly appreciated.

               

              Please find the attached packaged workbook

               

               

              Help needed with RegExpr Andrew Bickert

              • 4. Re: Help needed with RegExpr
                Rakesh Cherukuri

                Please find the new extracted workbook

                • 5. Re: Help needed with RegExpr
                  Andrew Bickert

                  I see, we should be able to fix that. I will take a look and get back to you

                  • 6. Re: Help needed with RegExpr
                    Rakesh Cherukuri

                    Thanks Andrew

                    • 7. Re: Help needed with RegExpr
                      Andrew Bickert

                      With the following formula we can get the numbers be we lose the decimal on the 0.025. I wasn't sure if you actually wanted this filed blank because it says "What I need = null" so figured I would clarify before continuing.

                       

                      IFNULL(
                      IFNULL(

                      IFNULL(

                      IFNULL(

                      IFNULL(

                      REGEXP_EXTRACT([Dose],'((\d.\d+ mg))'),

                      REGEXP_EXTRACT([Dose],'([\.,\d]+( MillionUnits)( MillionUnits)*)'))

                      ,REGEXP_EXTRACT([Dose],'([\.,\d]+( Units)( Units)*)'))

                      ,REGEXP_EXTRACT([Dose],'(\d ?(mL))'))

                      ,REGEXP_EXTRACT([Dose],'(\d ?(mg))'))
                      ,[Dose])

                       

                      1 of 1 people found this helpful
                      • 8. Re: Help needed with RegExpr
                        Rakesh Cherukuri

                        This is good Andrew, but please see the below screen,

                         

                        it is not null but .025 mg, sorry for that. I figured out a way not to loose the decimal by using the below calculation and cleared lot of other stuff with the help of your calculation.

                         

                        IF LEFT([Dose],1) = '.'

                        THEN STR('0' + [Dose])

                        ELSE

                        [Dose]

                        END

                         

                        But only one thing is missing now is when i have both , and . in the same field then I am missing the value before the , with the below expression

                         

                        IFNULL(

                        IFNULL(

                        IFNULL(

                        IFNULL(

                        IFNULL(

                        IFNULL(

                        REGEXP_EXTRACT([Dose  ],'((\d*,*.\d+ mg))'),

                        REGEXP_EXTRACT([Dose  ],'((\d*,*.\d+mg))')),

                        REGEXP_EXTRACT([Dose  ],'([\.,\d]+( MillionUnits)( MillionUnits)*)'))

                        ,REGEXP_EXTRACT([Dose  ],'([\.,\d]+( Units)( Units)*)'))

                        ,REGEXP_EXTRACT([Dose  ],'(\d ?( mg))'))

                        ,REGEXP_EXTRACT([Dose  ],'(\d ?( mL))'))

                         

                         

                         

                         

                        ,[Dose  ])

                         

                        Andrew Bicke

                        1 of 1 people found this helpful
                        • 9. Re: Help needed with RegExpr
                          Andrew Bickert

                          Hi Rakesh,

                          That was just the order of operation I got backwards, if we switch the ml below the mg (the bottom two expressions) it should work. Also, I added in the mg/kg for the bottom one. Combining your string into the formula would look like this with the following output.

                           

                          IF LEFT([Dose],1) = '.'

                          THEN STR('0' + [Dose])

                          ELSE

                          IFNULL(

                          IFNULL(

                          IFNULL(

                          IFNULL(

                          IFNULL(

                          IFNULL(

                          REGEXP_EXTRACT([Dose],'((\d.\d+ mg))'),

                          REGEXP_EXTRACT([Dose],'([\.,\d]+( MillionUnits)( MillionUnits)*)')),

                          REGEXP_EXTRACT([Dose],'([\.,\d]+( Units)( Units)*)')),

                          REGEXP_EXTRACT([Dose],'(\d ?(mg/kg))')),

                          REGEXP_EXTRACT([Dose],'(\d ?(mg))')),

                          REGEXP_EXTRACT([Dose],'(\d ?(mL))')),

                          [Dose])

                          END

                           

                           

                          Andrew

                          1 of 1 people found this helpful
                          • 10. Re: Help needed with RegExpr
                            Rakesh Cherukuri

                            Sorry I figured it out but only one problem now as I mentioned

                            But only one thing is missing now is when i have both , and . in the same field then I am missing the value before the , with the below expression

                             

                            IFNULL(

                            IFNULL(

                            IFNULL(

                            IFNULL(

                            IFNULL(

                            IFNULL(

                            REGEXP_EXTRACT([Dose  ],'((\d*,*.\d+ mg))'),

                            REGEXP_EXTRACT([Dose  ],'((\d*,*.\d+mg))')),

                            REGEXP_EXTRACT([Dose  ],'([\.,\d]+( MillionUnits)( MillionUnits)*)'))

                            ,REGEXP_EXTRACT([Dose  ],'([\.,\d]+( Units)( Units)*)'))

                            ,REGEXP_EXTRACT([Dose  ],'(\d ?( mg))'))

                            ,REGEXP_EXTRACT([Dose  ],'(\d ?( mL))'))

                             

                             

                            With ur new expression I am seeing this

                             

                            Andrew Bickert

                            • 11. Re: Help needed with RegExpr
                              Andrew Bickert

                              Hi Rakesh,

                               

                              This is the formula that I use to run against the clinical display line value in our EMR. I was trying to customize some of this logic into your first demo data so I apologize if I made it confusing. Since I don't have access to your full data I added your data into our extract and ran it against. I still get the decimal on the .025 issue (since our EMR will default to a 0) but thought I would at least let you try it to see if it can run against the rest of your data. I added the top REGEXP_EXTRACT([Dose],'(\d,\d*,*.\d+ mg)'), to accommodate your commas with the thousands place but you could modify that for your ml and MillionUnits if you needed.

                              Andrew

                               

                               

                              IFNULL(
                              if(FIND([Dose],"mg",0)>0) Then
                              IFNULL(
                              IFNULL(
                              IFNULL(

                              REGEXP_EXTRACT([Dose],'(\d,\d*,*.\d+ mg)'),
                              REGEXP_EXTRACT([Dose],'((\d.\d+ mg))')),
                              REGEXP_EXTRACT([Dose],'(\d\d (mg))')),
                              REGEXP_EXTRACT([Dose],'(\d (mg))'))
                              ELSEIF (find([Dose],"mcg",0)>0) then
                              IFNULL(
                              IFNULL(
                              REGEXP_EXTRACT([Dose],'((\d.\d+ mcg))'),
                              REGEXP_EXTRACT([Dose],'(\d\d (mcg))')),
                              REGEXP_EXTRACT([Dose],'(\d (mcg))'))
                              ELSEIF (find([Dose],"MillionUnits",0)>0) then
                              IFNULL(
                              IFNULL(
                              REGEXP_EXTRACT([Dose],'((\d\d.\d\d+ MillionUnits))'),
                              REGEXP_EXTRACT([Dose],'(\d\d (MillionUnits))')),
                              REGEXP_EXTRACT([Dose],'(\d (MillionUnits))'))
                              ELSEIF (find([Dose],"mL",0)>0) then
                              IFNULL(
                              IFNULL(
                              REGEXP_EXTRACT([Dose],'((\d.\d+ mL))'),
                              REGEXP_EXTRACT([Dose],'(\d\d (mL))')),
                              REGEXP_EXTRACT([Dose],'(\d (mL))'))
                              END,[Dose])

                              • 12. Re: Help needed with RegExpr
                                Rakesh Cherukuri

                                Thanks Andrew. I will check it.