removing alpha suffix from ID

[nopposan]

Hello all. Here at my workplace we use something we call an accession number for patient samples. There's a manually applied labeling system that uses a six digit number that is sometimes followed by a lettered suffix. The lettered suffix usually indicates that the number has been used before on a relative of the current patient; i.e., it's used to relate the mother's sample to the child's via a common number but with a suffix denoting the relationship. Anyway . . .

I want to write a calculation that will remove all letters from the aforementioned manually applied ID number. The output field values can be used to relate samples to one-another, probably through a separate table. So far, the only solution I've come up with is to just copy the first six characters from the ID using a Left(ID);6 function. The trouble is that I never know when this function will become obsolete; I anticipate that because the six digits are based on the year and the sequential accumulation of samples in that year . . . either an increase in the number of samples or the year 2010 will cause the number of essential digits in the ID to increase. (The only thing that prevented a change in the number of digits this year was the decision to discontinue the practice of including a leading zero.)

So, how do I remove all letters from an ID that contains both letters and numbers?

[comment]

Try:

Filter ( ID ; "0123456789" )

Or, if it's OK to remove leading zeros:

GetAsNumber ( ID )

[Lee Smith]

There are a couple of ways to parse out the numbers, depending on the data.

I would create a new calculation field, and filter the numbers.

Filter (YourField; 0123456789) will remover everything except the numerical digits.

However, if there are Alpha characters that need to be retained, i.e. a leading Alpha, or one in the middle, then you might use the Left (YourField; [color:blue]7) or the [color:blue]number of digits before the characters that you need to parse out.

If these don't help, then post a sample of the data, or a copy of the file.

Lee

[nopposan]

The filter thing will work fine! (That is unless they change the naming convention dramatically.) Thank you!

[nopposan]

Well, there are very rare occasions when a suffix contains a number following the letter(s) denoting the familial relationship. Therefore I used the following compromise:

Trim(Filter(Left(Accession_number;7);1234567890))

It's interesting that if I put the 0 at the beginning of the series to filter, it gets omitted from the calculation and yields erroneous results.

[Raybaudi]

That is because you haven't write the calc correctly...

the filter must be inside quotes: "0123456789"

If not, FM will get that as a number, omitting the leading zero

[Lee Smith]

There usually are exceptions when the Users have the ability to enter the data, that is why I suggested that you submit a sample of the data, or a sample file.

Lee

[Søren Dyhr]

Explain please include both field types for source as well as recieverfield??? Samples of data would be fine too?

Ah! Daniele found it!

--sd

Edited May 14, 2008 by Guest

[comment]

there are very rare occasions when a suffix contains a number following the letter(s)

Now you tell us... I think you should work from the opposite direction, then:

Let ( [

alpha = Filter ( Upper ( ID ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 

char = Left ( alpha ; 1 ) ; 

pos = Position ( ID ; char ; 1 ; 1 )

] ;

Case ( pos ; Left ( ID ; pos - 1 ) ; ID )

)

[nopposan]

Explain please include both field types for source as well as recieverfield??? Samples of data would be fine too?

Both the source and the reciever fields are defined as text fields.

Sample data (Parantheses are comments.):

078432 (Patient sample from 2007.)

805648 (Patient sample.)

805648M (Sample specimen from patient's mother.)

805648P (From patient's father.)

805648F (Also from patient's father; old nomenclature.)

805648M2 (Second sample given by patient's mother; the first sample probably didn't work or didn't yield enough DNA.)

805648R (Second sample given by patient.)

805648GM (Grandmother's sample.)

805648GP (Grandfather's sample.)

As you can see, the accession number is used to convey much more information than a simple unique identifier does (year received, chronology of receipt, patient or relative, which relative, and whether the same patient or relative has submitted a sample before); I might work on some scripts that will automatically populate fields, such as gender, based on this information and some inductive logic, but that's not a high priority right now.

'Sorry about not mentioning the possibility of numerals following the lettered suffix; it was only one sample out of about 3300, and I didn't even notice it myself until I tried out the calculation.

Thanks for letting me know about putting the quotes around the filter. Also, thanks for the calculation that would "work backwards." I've yet to experiment with it though.

To be continued . . .

[Lee Smith]

The more you post, the less I understand what you are after.

If it is just the left 6 digits, as you have indicated, then all you need is a calculation using the Left Function

i.e.

Left (YourTextField; 6)

If there is more to it than that, lets break it out in parts. Let us know what else you want parsed.

Lee

[nopposan]

Well, for now, the Left function will do the trick, Lee. I was concerned about the future when the number of digits might increase, but I guess I'll just cross that bridge when I come to it.

Let's end this post.

Thanks for all of your advice.