-
1. Re: Help needed with RegExpr
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
-
-
3. Re: Help needed with RegExpr
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
-
-
5. Re: Help needed with RegExpr
I see, we should be able to fix that. I will take a look and get back to you
-
-
7. Re: Help needed with RegExpr
1 of 1 people found this helpfulWith 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])
-
8. Re: Help needed with RegExpr
1 of 1 people found this helpfulThis 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 ])
-
9. Re: Help needed with RegExpr
1 of 1 people found this helpfulHi 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
-
10. Re: Help needed with RegExpr
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
-
11. Re: Help needed with RegExpr
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]) -
