3 Replies Latest reply on Jul 5, 2011 5:01 PM by damondidit

    A bad Case of PatternCount(s) and GetAsNumbers

    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

        • 1. Re: A bad Case of PatternCount(s) and GetAsNumbers
          philmodjunk

          Why do you need to add the thousands separator in this fashion?

          Let ( [ N = GetAsNumber ( OriginalNumbers) ;
                    GT = If ( PatternCount ( OriginalNumbers; "<" ) ; "<" )
                  ] ;
                    GT & If ( N >= 1000 ; Div ( N ; 1000 ) & "," & Right ( "00" & Mod ( N ; 1000 ) ; 3 ) ; N )
                )Let ( [ N = GetAsNumber ( OriginalNumbers) ;
                    GT = If ( PatternCount ( OriginalNumbers; "<" ) ; "<" )
                  ] ;
                    GT & If ( N >= 1000 ; Div ( N ; 1000 ) & "," & Right ( "00" & Mod ( N ; 1000 ) ; 3 ) ; N )
                )

          The thousands separator can be part of the number's specified data format on the layout and if this is the only reason for needing the 1000's separator, you can then use that data format on your label and use two calculation fields:

          cGT:  If ( PatternCount ( OriginalNumbers; "<" ) ; "<" )

          cCleanedNumber: getasnumber ( originalnumbers )

          On your layout you can put the two fields together to display the desired result, most likely by using them as merge fields.

          <<YourTable::cGT>><<YourTable::cCleanedNumber>>

          • 2. Re: A bad Case of PatternCount(s) and GetAsNumbers
            damondidit

            Thank you for your help Phil.

            This is where I am, just need to work on getting the second thousands separator to work. Right now it is eating one of my zeroes.

            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  &  

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

            & DD & ND & z0 & JF)

            • 3. Re: A bad Case of PatternCount(s) and GetAsNumbers
              damondidit

              I'm all right now, here is what I ended up with:

              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)