5 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    Getting Data From Adjacent Cell

    ruby _

      http://i33.tinypic.com/nl4ksj.png[/img]

       

      Picture isn't showing up. But the top is what it is currently doing and the bottom in red is what it needs to do. Mainly it's field 5 that I am concerned with.

       

      Field 5 is a calculated field and I'm using the formula:

      Right(Field1, 4) + Right(Field4, 3)[/CODE]

       

      So it takes the right most 4 digits of field 1 and combines it with the 3 digits in field 4. If a field is null in field 1 it needs to get the data above it. Does that make sense? How can you do that in Monarch?

        • Getting Data From Adjacent Cell
          Grant Perkins

          Hi Ruby and welcome to the forum.

           

          Within a single model calculated fields have to work with data from the same record - i.e. across for the row.

           

          Assuming that your example is from the Table extracted, if your Field 1 is from an APPEND template of some sort (not the DETAIL template) then you can likely make the field fill in the table based on the value of the previous record. That should make the calculation work for you.

           

          If the field is from the detail record that will not work. (This seems unlikely but I am including it just in case).

           

          In that situation the most common approach would be to extract what you can and export it to a new file. Then use that file with another model and do as suggested above in order to fill the field.

           

           

          Here is the relevant section of the Help file that explains the purpose of the "Copy from previous record" option.

           

          ======================================

           

          Empty Cells options (non-calculated fields only)[/B]

           

           

           

          Many reports are designed so that duplicate field values do not repeat down the page. When Monarch extracts the data from the report and builds a database table, it does not automatically duplicate these values, leaving empty cells throughout the field.

          [B]Leave blank[/B] (non-calculated fields only) - Select this option if you prefer to leave the empty cells blank.

          [B]Copy value from previous record[/B] (non-calculated fields only) - Select this option to copy the appropriate values into the empty cells.

          /LIST======================================

           

          If that does not lead you to a solution let us know and we can seek an alternative approach.

           

          HTH.

           

           

          Grant

            • Getting Data From Adjacent Cell
              Olly Bond

              Hello Grant,

               

              I suspect that if the field is blank in some rows it is because it's been trapped as Detail instead of as Append.

               

              Hello Ruby,

               

              Please could you post a sample of the report between and tags? The problem might be trivial if we can get the templates right, otherwise we might need a second model to make the data behave...

               

              Best wishes,

               

              Olly

                • Getting Data From Adjacent Cell
                  ruby _

                  That was so simple!!! Brilliant! Thanks so much for your help. Monarch seems so robust, but a little weird in figuring how it works. I'm so used to excel.

                    • Getting Data From Adjacent Cell
                      Nick Osdale-Popa

                      That was so simple!!! Brilliant! Thanks so much for your help. Monarch seems so robust, but a little weird in figuring how it works. I'm so used to excel.[/QUOTE]

                       

                      The best way to think of the data when you are in Monarch is to view it as a database table, since that's exactly what it is. The rows are independent of one another, unlike Excel where you can make a row affect another.

                        • Getting Data From Adjacent Cell
                          Grant Perkins

                          The best way to think of the data when you are in Monarch is to view it as a database table, since that's exactly what it is. The rows are independent of one another, unlike Excel where you can make a row affect another.[/quote]

                           

                          What Nick said.

                           

                          Monarch and Excel together can make a very powerful solution to most data working challenges when you a familiar with which strengths each bring to the game.

                           

                          You may find the observations at [URL="http://www.********************"]www.********************[/URL] help to provide a productive perspective - and more.

                           

                           

                          Grant