5 Replies Latest reply: May 15, 2014 10:15 AM by Olly Bond RSS

    Formula in Calculated Field - Need Help

    a2z143 _

      Need help in creating a formula (calculated field) to take current file and return as shown below.

       

       

      Current file:

      Name                        Amt1        Amt2         Amt3        Amt4        Amt5          Amt6       Date

      ONE,KELLY T     48.46     0     48.46     0     0     48.46     8022013

      ONE,JULIE                     60.62     0     30.31     30.31     0     60.62     8022013

      ONE,ALIMA     0     0     41.68     0     0     41.68     8022013

      ONE,BECKY A     0     115.63     33.04     82.6     0     115.64     8022013

      ONE,SALLY K     135.13     0     38.61     96.52     0     135.13     8022013

       

      Return should look like this:

      Name                       Source     Amt

      ONE,KELLY T     CS1        48.46

      ONE,KELLY T     CS2        0

      ONE,KELLY T     CS3        48.46

      ONE,KELLY T     CS4        0

      ONE,KELLY T     CS5        0

      ONE,KELLY T     CS6        48.46

        • Formula in Calculated Field - Need Help
          Olly Bond

          Hello a2z,

           

          This needs the variable width multiple column region trick. I think there's an example on my blog working from a PDF file - please have a look and if it's not clear then please let me know.

           

          Best wishes,

           

          Olly

            • Formula in Calculated Field - Need Help
              a2z143 _

              cant locate it on your blog.

                • Formula in Calculated Field - Need Help
                  Olly Bond

                  Hello a2z,

                   

                  It's the post on Inconsistent Columns - http://blog.greenbar.info/post/24328481186/inconsistent-columns[/url] - that has the technique you can use here. Is your input data a report, or a CSV file or are you working from a database?

                   

                  Best wishes,

                   

                  Olly

                    • Formula in Calculated Field - Need Help
                      a2z143 _

                      the file I'm using is csv

                        • Formula in Calculated Field - Need Help
                          Olly Bond

                          Hello a2z,

                           

                          OK, then the trick is to rename it as a .txt file. Open it as a report, not as a database, and then define a multi-column region, with 6 columns, each 1 character wide, starting in column 100 (or however many you need to start the MCR to the right of the data).

                           

                          Then make a detail template that traps every line - a simple trap like a non-blank wildcard in the first position on every line would do. Select a field that includes the entire line, and called this field "Data", make it a character. Let's assume for the moment that each line is less than 254 characters wide in total - let me know if that's a problem.

                           

                          Now in the Table window, make three calculated fields, all numeric, called "Column" with the expression Column(), and Line with the expression Line(), and Page with the expression Page(). Now define a sort order based on Page (ascending), then Line (ascending) followed by Column (ascending) - this should show you data like:

                           

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          ONE,KELLY T 48.46 0 48.46 0 0 48.46 8022013

                          /CODE

                           

                          To get the data you want, define two fields "Left" and Right as follows:

                           

                          "Left" should be lsplit(intrim(Data);3;" ";1)+lsplit(intrim(Data);3;" ";2)

                           

                          "Right" should be lsplit(extract(intrim(Data);Left;"");6;" ";Column())

                           

                          I'm assuming your data is exactly as shown in your sample - if that's just an approximation and you'd like some help with the real data, please send me a mail and I'll give you the corrected expressions for the fields.

                           

                          What's going on here? Well, using the MultiColumnRegion lets you create six rows of data for each record in your report. Using lsplit() lets you break up each row of data into a chunk that corresponds with the Column() value. This technique only works with report files (text, PDF etc) and not with database sources (Excel, CSV etc), at least in the current version of Monarch.

                           

                          Hope this helps,

                           

                          Olly