Improve Formula

[dunkyn]

I have some security descriptions that need some cosmetic work. Any suggestions on a more efficient setup to do the following?

If(InStr ({cot31203.Security},&quot;DTD&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;DTD&quot)-1) else

If(InStr ({cot31203.Security},&quot;PASS THROUGH&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;PASS THROUGH&quot)-1) else

If(InStr ({cot31203.Security},&quot;NOTES&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;NOTES&quot)-1) else

If(InStr ({cot31203.Security},&quot;CORP&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;CORP&quot)-1) else

If(InStr ({cot31203.Security},&quot;INC&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;INC&quot)-1) else

If(InStr ({cot31203.Security},&quot;LTD&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;LTD&quot)-1) else

If(InStr ({cot31203.Security},&quot; &&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot; &&quot)-1) else

If(InStr ({cot31203.Security},&quot;DEB&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;DEB&quot)-1) else

If(InStr ({cot31203.Security},&quot;COM&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;COM&quot)-1) else

If(InStr ({cot31203.Security},&quot;(&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;&quot)-1) else

If(InStr ({cot31203.Security},&quot; INC&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot; INC&quot)-1) else

If(InStr ({cot31203.Security},&quot; SER &quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot; SER &quot)-1) else

{cot31203.Security}

TIA

 

[lyanch]

What are you trying to chop off? Is it the same for all (or at least most)
of the records?

Lisa

 

[dunkyn]

Each If statement represents text editing for a defined situation - just to make the text more readable.

Examples:

Removing the word(s) INC, CORP, NOTES, DTD 12/31/2002 (maturity date already in description, DEB, etc.

I just don't know how to wrap this up in a select statement

 

[synapsevampire]

Consider using Replace:

Replace ({cot31203.Security},(,&quot; INC &quot;, &quot;&quot
Replace ({cot31203.Security},(,&quot; DTD &quot;, &quot;&quot
etc...

-k

 

[dunkyn]

Replace is a good idea...

One thought...the DTD (dated) is followed by random dates. How can I use the Replace function if the date varies?

Example:

DTD 3/31/2002
DTD 12/15/2000

Etc. What I have been doing is deleting all text after the &quot;found&quot; string.

?

 

[Ngolem]

One observation I have is that you are performing the In-string function twice as often as necessary


If(InStr ({cot31203.Security},&quot;NOTES&quot)<>0 then
Left({cot31203.Security},(InStr ({cot31203.Security},&quot;NOTES&quot)-1) else

There is no real reason to have &quot;else&quot; conditions here since none of the conditions are the same.

To simplify this formula you can use an array of conditions and a for-next loop to do the evaluation

Something like

WhilePrintingRecords;
// put all characters in an array
Stringvar array Test := [&quot;NOTES&quot;,&quot;DTD&quot;,.......];
numberVar icount;
StringVar result := {cot31203.Security};

for icount := 1 to ubound(test) do
(
If(InStr ({cot31203.Security},Test[icount]))<>0 then
(
result := Replace ({cot31203.Security},Test[icount], &quot;&quot;
exit for;
);
);

result;

We are assumming here that there is only one occurance of a problem piece of text and no repeat occurances .

Replace as SV suggested will only remove the offending characters.

I may have made a syntax error or two...I am still in Thailand and don't have my reference books with me.....but this approach does simplify your formula a lot.

hope it helps

Jim Broadbent