Calculation assistance/boot in the right direction

[dayhox]

I get numeric results from various outside entities, and for some reason they are unwilling to change the way they output their numbers to save my sanity.

Values come in as positive numbers

1,000,000

100,100

1,000

<100

1000J

ND

--

[] empty etc.

basically they come in in all various formats, thousands separator, less than, "J" flags, ND, --

I have the following cobbled together from various sources, but am still missing some possible results:

Let ( 

[ 

N = GetAsNumber (OriginalNumbers );

GT = If(PatternCount (OriginalNumbers;"<");"<"); - greater than

JF = If (PatternCount (OriginalNumbers;"J");"J"); - j flag

DD = If(PatternCount (OriginalNumbers;"--");"--"); - double dash

ND = If(Exact ( OriginalNumbers ; "ND");"ND"); - non detect

z0 = If(IsEmpty (OriginalNumbers);"--"); - zayroh

Lz = If(PatternCount (OriginalNumbers;"0.");"0"); - leading zero

Tz = If(PatternCount (OriginalNumbers;".0");".0") - trailing zero

];GT  &  Lz &

If ( N >= 1000 ; Div ( N ; 1000 ) & "," & Right ( "00" & Mod ( N ; 1000 ) ; 3 ) ;

 N )

& Tz & DD & ND & z0 & JF)

As it is now, if my input number is .09J for instance, my row looks like .09.0J (should read .09J) Due to my Tz but I need to account for numbers that get input as 5.0 and are stripped of the Trailingzero leaving just 5

also, and I realize I am not accounting for it in the above, but there is no second thousandths separator for numbers >999,999 haven't figured out how to add into the existing If

Thanks for any thoughts, boots, ideas,

d

[comment]

I would suggest you use two calculation fields: one to return a number (if applicable) and one for the qualifying flag. This will also save you the trouble of formatting the number.

[dayhox]

This is what ended up working for me:

Let ( [ N = GetAsNumber (OriginalNumbers );



GT = If(PatternCount (OriginalNumbers;"<");"<");



JF = If (PatternCount (OriginalNumbers;"J");"J");



DD = If(PatternCount (OriginalNumbers;"--");"--");



ND = If(Exact ( OriginalNumbers ; "ND");"ND");



z0 = If(IsEmpty (OriginalNumbers);"--")



];



GT  &  Case(



N   ≥  1000000; Left (  N ; 1 ) & ","&Middle ( N  ; 2 ; 3) & ","& Right ( N ; 3 );

N  ≥  100000; Left (  N ; 3 ) & ","& Right (  N ; 3 );

N  ≥  10000; Left (  N ; 2 ) & ","& Right (  N ; 3 );

N  ≥  1000; Left (  N ; 1 ) & ","& Right (  N ; 3 );



N  < 1 and N > 0; Left (N; 0) & "0"& N;



 N)



& DD & ND & z0 & JF)

Thanks for your suggestion. Using the 2 calc fields, I just can't get my head around the breaking out into separate fields and then recombining into a 4th field, do you have a simplistic example?

Thanks again,

d

[comment]

The number calculation could probably be just =

OriginalField

with the result type set to Number.

I am not sure about the other field, because it seems these markings are supposed to mean something - but I haven't got a clue what that is.

I don't see why you should need to recombine the results into a 4th field.

[dayhox]

To clear up the non-numerics - These inputs are chemical analysis from a lab. All of the analytes have different reporting values.

Some are less than the reporting limit <, some are non-detect (ND), some are ND< non-detect less than the reporting value, some are zero in which a -- has to go in and some are way over the allowable range signified by the J flag.

These are all going into a table output with each analyte side by side in columnar fashion. I can't set the field to numeric because, while the number gets properly formatted, I will loose any of the non numeric flags associated with the analysis. So I have this calculation run on the data uploaded from a labs csv file. Some labs include proper thousandths formatting, and others do not. I pull out the numbers and the non numerics, format the number, and recombine everything into the single text value for the field.

Hope that is clearer.

As far as the 4th field, I think I just mis understood your original reply. What I have works, and is the solution that, while a bit clunky, fits my level of expertise with FMP.

Thanks again,

d