AnsweredAssumed Answered

A bad Case of PatternCount(s) and GetAsNumbers

Question asked by damondidit on Jun 14, 2011
Latest reply on Jul 5, 2011 by damondidit

Title

A bad Case of PatternCount(s) and GetAsNumbers

Post

I have analytical lab data that gets imported by the bulk into my FMPDB. The data comes in with various "flags" and attributions some with thousands separator and most without.

What I am attempting to do is take this imported data as it is and move it into a new calculation field with the transformations that I require.

The values that come in look like: 10,000 ; 10,000J 1000J ; 100000 ; ND<10000 ; N/A ; etc.

I have the following which seems to do most of what I want, but when I get a combo of things that are happening that's when all seems to break up.

Case (
PatternCount ( OriginalNumbers ;"ND<" ); Substitute(OriginalNumbers; "ND"; "");
PatternCount ( OriginalNumbers ;" J" ); OriginalNumbers;
(GetAsNumber ( OriginalNumbers )) >= 1000000; Left (  OriginalNumbers ; 1 ) & ","& Middle (  OriginalNumbers ;2; 3 )& ","& Right (  OriginalNumbers ; 3 );
(GetAsNumber ( OriginalNumbers )) >= 100000; Left (  OriginalNumbers ; 3 ) & ","& Right (  OriginalNumbers ; 3 );
(GetAsNumber (OriginalNumbers )) >= 10000; Left (  OriginalNumbers ; 2 ) & ","& Right (  OriginalNumbers ; 3 );
(GetAsNumber ( OriginalNumbers )) >= 1000; Left (  OriginalNumbers ; 1 ) & ","& Right (  OriginalNumbers ; 3 );
Case (PatternCount ( OriginalNumbers ;"," ); OriginalNumbers;
))

I need some help with getting my cases in order or nesting them so that: If there is a prefixed ND<, the ND is obilterated leaving the < ; If there is a postfixed J, my thousands separator doesn't eat my last 0 ; all numbers that go in need to have the thousands seperator on their way out. I cannot change the way the data gets in as it is a historical document from the lab so I need to run these transformations on the data I output.

Thank you for your eyes and a boot in the right direction.

d

Outcomes