7 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Delimited string - field definition

    wcc7106a _

      The following is a snapshot from a report that is all I have available.

       

       

      6897 7996010.8101.0.4215.453.41.0.0                 31-JUL-07  55105.7720.01                                     0.00            9,585.81

      6898 8140010.8301.0.4215.455.41.0.0                 31-JUL-07  55999.8310.01                                     0.00            3,392.27

      6899 8460010.6505.[B]004915.4215.320.41.0.0[/B]            31-JUL-07  529804.8415.01                                    0.00              867.76

      6900 8460010.6505.004915.4215.320.41.0.0            31-JUL-07  529804.8415.02                               11,350.60                0.00

      6901 8460010.7101.004915.4225.453.41.0.0            31-JUL-07  45200.8415.01                               634,297.53                0.00

      6902 8460010.8301.004915.4215.455.41.0.0            31-JUL-07  55999.8415.02                                30,660.60                0.00

      6903 8460010.8301.004915.4215.455.41.0.0            31-JUL-07  55999.8415.01                                62,490.66                0.00

      6905 8910010.8101.0.4215.455.41.0.0                 31-JUL-07  55999.8620.01                                     0.00          189,304.37

       

       

      The G/L account structure consists of 8 segments separated by a ".".  The 1st two segments are a fixed width so defining fields is not a problem.  After the variable fields thereport lines up correctly.

       

      The remaining segments are the problem.  I tried defining the variable width segments as one field.  I then used Excel's "Column to Text" to split them apart.  I got close to what I wanted but Excels' dropping leading zeros makes thsi approach unacceptable.

       

      I would prefer to do the whole operation in MONARCH.  Obviously looking for something that I can run from a batch file.  The dropping of leading zeros is a

      real problem as the Chart of Accounts is what /I]i inherited.

       

      Would appreciate any suggestions.

       

      Bill

        • Delimited string - field definition
          wcc7106a _

          Hopefully, this will pesent better!   New to posting!

           

          6897 7996010.8101.0.4215.453.41.0.0                 31-JUL-07  55105.7720.01                                     0.00            9,585.81

          6898 8140010.8301.0.4215.455.41.0.0                 31-JUL-07  55999.8310.01                                     0.00            3,392.27

          6899 8460010.6505.004915.4215.320.41.0.0            31-JUL-07  529804.8415.01                                    0.00              867.76

          6900 8460010.6505.004915.4215.320.41.0.0            31-JUL-07  529804.8415.02                               11,350.60                0.00

          6901 8460010.7101.004915.4225.453.41.0.0            31-JUL-07  45200.8415.01                               634,297.53                0.00

          6902 8460010.8301.004915.4215.455.41.0.0            31-JUL-07  55999.8415.02                                30,660.60                0.00

          6903 8460010.8301.004915.4215.455.41.0.0            31-JUL-07  55999.8415.01                                62,490.66                0.00

          6905 8910010.8101.0.4215.455.41.0.0                 31-JUL-07  55999.8620.01                                     0.00          189,304.37[/CODE]

            • Delimited string - field definition
              Data Kruncher

              Hi Bill, and welcome to the forum!

               

              You're really close to the solution. Your idea of initially capturing the whole thing as one big field is a good one, so do that.

               

              I did, and named that field "A", for convenience. Then you need to build eight calculated fields to break the A field apart properly. Each of the calculated fields (each defined to be character type) will have a similar expression. The first segment will have:

               

              LSplit(A,8,".",1)[/code]

               

              the next will have:

               

              LSplit(A,8,".",2)[/code]

               

              and so on, incrementing the last value by one, up to the last, which will be:

               

              LSplit(A,8,".",8)[/code]

               

              Have a look at LSplit in the online help. It has a related function that you may use some other time as well, RSplit.

               

              Kruncher