Extract from string

[gromboy]

Hi,
I have a sql server db which has a field in a table called DRUGDETS. In this field is the following:
Drugname Drugstrength Unit of measure, Route, Vehicle

Example
CISPLATIN 50 mg, IV Infusion, Saline 0.9%

I want to be able to report on the Drugname and route
eg CISPLATIN IV INFUSION

There might be different routes for each drug so what I'm trying to do is report on all the CISPLATIN IV INFUSION, then all the CISPLATIN IV BOLUS as seperate entities. The drugname might be 2 words, the dose is always a number.

Any help greatly appreciated as ever !
Steve

 

[IanWaterman]

Steve

//{@Route}:
stringvar array x := split({Fieldname},",");
x[2]

//{@drug}:
stringvar array x := split({@Drug+amt}," ");
if x[4] = 'mg,' then x[1]&" "&x[2] else x[1]

Ian

 

[gromboy]

Ian,
Your formulas work brilliantly... The only problem is I've made a schoolboy error and not given you all the facts. When I came to run on my entire db there are some preparations which have 2 drugs in them !
eg
IFOSFAMIDE 3680 mg, MESNA 3700 mg, IV Infusion, Saline 0.9%

Sorry I didnt realise this, I have also discovered the "route" is actually available by linking to another table...but not the individual drug name.

I guess what I really need is the drugname extracting, but if there are 2 drugs then these displaying as "IFOSFAMIDE and MESNA". There can be up to 4 drugs in the application....aaahhh this is becoming more difficult.

Not sure if there is a workaround for this
I've attached my report if it helps ?
Steve

 

[IanWaterman]

Its going to be a bit clunky but you could try

//{@drug}:
stringvar array x := split({FieldName},",");
if right(x[4],2) = 'mg' then x[1]&" "&x[2]&" "&x[3]&" "&x[4] else

if right(x[3],2) = 'mg' then x[1]&" "&x[2]&" "&x[3] else
if right(x[2],2) = 'mg' then x[1]&" "&x[2] else
x[1]

Ian

 

[gromboy]

Hi Ian,
Thanks for the update... the formula editor does not throw an error but when exit to the Preview screen...
I seem to be getting the following error:
A subscript must be between 1 and the size of the array.

Its highlighting the following x[4]

Its very rare to have up to 4 drugs, I also may not have in my db if that is the issue ?

Steve

 

[IanWaterman]

Try commenting out the 4 drug test

//{@drug}:
stringvar array x := split({FieldName},",");
//if right(x[4],2) = 'mg' then x[1]&" "&x[2]&" "&x[3]&" "&x[4] else

if right(x[3],2) = 'mg' then x[1]&" "&x[2]&" "&x[3] else
if right(x[2],2) = 'mg' then x[1]&" "&x[2] else
x[1]


Ian

 

[gromboy]

Its now putting the strength back in against the drug, rather than just the drugname

Steve

 

[IanWaterman]

OOPs!

This works fine on one record, no idea how fast it will be with multiple records, replace my text with your field name.

stringvar array x := split('CISPLATIN 50 mg, CISPLATIN2 50 mg, CISPLATIN3 50 mg, IV Infusion, Saline 0.9%',",");

stringvar y1:='';
stringvar y2:='';
stringvar y3:='';

//if right(x[4],2) = 'mg' then x[1]&" "&x[2]&" "&x[3]&" "&x[4] else

if right(x[3],2) = 'mg' then y3:=x[3];
if right(x[2],2) = 'mg' then y2:=x[2];
y1:=x[1];


Stringvar array z1:= split(y1," ");

Stringvar array z2:= split(y2," ");

Stringvar array z3:= split(y3," ");

z1[1]&" "&z2[2]&" "&z3[2]

Ian

 

[lbass]

You need to test for the number of elements in the array, using the ubound function.

Try the following:

stringvar x := {table.yourfield};
stringvar y := left(x,instrrev(x,"mg")+1);
stringvar array z := split(y,", ");
stringvar k;
numbervar i;
numbervar j := ubound(z);

for i := 1 to j do(
k := k + split(z," ")[1]+" "
);
k

-LB

 

[IanWaterman]

LB's solution is much more efficient.

Ian

 

[gromboy]

I've tried the lb one...

I appear to be getting an error "A variable cannot be redeclared with a different type"

At this point stringvar x

Any ideas ?
Steve

 

[IanWaterman]

I had that problem too.

It was because I created a new formula leaving the others still active. Variable X is used in those. If you remove the old formula form report it should work, or change var x to XX.

Ian

 

[gromboy]

It now seems to be adding all previous drugs together to create one long string.....!
FLUOROURACIL EPIRUBICIN CYCLOPHOSPHAMIDE EPIRUBICIN IFOSFAMIDE MESNA TRASTUZUMAB ETOPOSIDE IFOSFAMIDE MESNA FLUOROURACIL EPIRUBICIN CYCLOPHOSPHAMIDE EPIRUBICIN IFOSFAMIDE MESNA DOXORUBICIN B VINBLASTINE DACARBAZINE DOCETAXEL IFOSFAMIDE MESNA DOCETAXEL CYTARABINE CYCLOPHOSPHAMIDE ETOPOSIDE IFOSFAMIDE MESNA IFOSFAMIDE MESNA ETOPOSIDE IFOSFAMIDE MESNA ETOPOSIDE IFOSFAMIDE MESNA DOXORUBICIN CYCLOPHOSPHAMIDE METHOTREXATE FLUOROURACIL DOXORUBICIN B VINBLASTINE DACARBAZINE B VINBLASTINE DACARBAZINE CYTARABINE ETOPOSIDE DAUNORUBICIN DAUNORUBICIN CYCLOPHOSPHAMIDE FLUOROURACIL RITUXIMAB VINCRISTINE DOXORUBICIN CYCLOPHOSPHAMIDE FLUOROURACIL EPIRUBICIN CYCLOPHOSPHAMIDE TEST GEMCITABINE CARBOPLATIN FLUOROURACIL EPIRUBICIN CYCLOPHOSPHAMIDE METHOTREXATE EPIRUBICIN FLUOROURACIL EPIRUBICIN CYCLOPHOSPHAMIDE VINORELBINE IFOSFAMIDE MESNA IFOSFAMIDE MESNA VINCRISTINE DOXORUBICIN CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE MESNA CISPLATIN FLUOROURACIL CARBOPLATIN EPIRUBICIN CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE MESNA DOXORUBICIN VINBLASTINE VINBLASTINE DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN VINBLASTINE VINBLASTINE VINBLASTINE VINBLASTINE DOXORUBICIN VINBLASTINE DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN DOXORUBICIN CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE EPIRUBICIN CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA CYCLOPHOSPHAMIDE MESNA FLUOROURACIL MESNA FLUOROURACIL MESNA ETOPOSIDE MESNA ETOPOSIDE MESNA

 

[IanWaterman]

Try adding local to all vars eg

local stringvar x := {table.yourfield};

Ian

 

[gromboy]

That seems much better... but its still not quite right...

For example the data item says
CYCLOPHOSPHAMIDE 860 mg, MESNA 17 mg, POTASSIUM CHLORIDE 17 mmols, IV Infusion, Saline 0.9%

And the formula outputs
CYCLOPHOSPHAMIDE MESNA


Is this because the POTASSIUM CHLORIDE is 2 words ?
Steve

 

[IanWaterman]

Not sure.

What does my solution return?

Ian

 

[gromboy]

Not tried yours as you said it was a bit clunky...

Do I replace the CISPLATIN with the drugname field in every instance ?

stringvar array x := split('CISPLATIN 50 mg, CISPLATIN2 50 mg, CISPLATIN3 50 mg, IV Infusion, Saline 0.9%',",");

stringvar y1:='';
stringvar y2:='';
stringvar y3:='';

//if right(x[4],2) = 'mg' then x[1]&" "&x[2]&" "&x[3]&" "&x[4] else

if right(x[3],2) = 'mg' then y3:=x[3];
if right(x[2],2) = 'mg' then y2:=x[2];
y1:=x[1];


Stringvar array z1:= split(y1," ");

Stringvar array z2:= split(y2," ");

Stringvar array z3:= split(y3," ");

z1[1]&" "&z2[2]&" "&z3[2]

 

[lbass]

The reason is that I assumed all amounts were expressed in 'mgs'. What are the possible variations in this?

-LB

 

[gromboy]

Could be any number...... sorry...this is becoming more complicated

capsule
Gram
IU
kilo unit
L
liquid
mega unit
mg
microgram
ml
mmols
MU
suppository
tablet
unit
vial

 

[lbass]

Let me ask a different question. Are there always two comma-delimited elements (route, vehicle) to the right of the drug elements?

-LB