2 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    Calculated Field Referencing Previous Record

    TPAUL _

      Is there a way to reference the previous record in a Calculated field in V8.0?

      I am importing an Excel Spreadsheet where there is a Header line within the database. I am trying to make a Calculated field to say if the value of column2 = Regional MANAGER:, then COlumn2 , ELSE: the value of the previous record. As the hEader line in the Excel Database is not repeated - I want to repeat the previous field's value. Thus appending the Regional Manager field to each record.

       

      EG Layout

       

      Col1    Col2              Col3       Col4

      Br_No   Branch Name       Br Manager Phone 

              Regional Manager: Joe Schmo

      001     Boston Branch     Bobby Orr  603 5551212

      002     Detroit Branch    Joe Louis  456 1234567

      003     New York Branch   Phil Espos 212 5551234

       

      THANKS

        • Calculated Field Referencing Previous Record
          Grant Perkins

          Originally posted by TPAUL:

          Is there a way to reference the previous record in a Calculated field in V8.0?

          I am importing an Excel Spreadsheet where there is a Header line within the database. I am trying to make a Calculated field to say if the value of column2 = Regional MANAGER:, then COlumn2 , ELSE: the value of the previous record. As the hEader line in the Excel Database is not repeated - I want to repeat the previous field's value. Thus appending the Regional Manager field to each record.

           

          EG Layout

          [font="courier"] 

          Col1    Col2              Col3       Col4

          Br_No   Branch Name       Br Manager Phone 

                  Regional Manager: Joe Schmo

          001     Boston Branch     Bobby Orr  603 5551212

          002     Detroit Branch    Joe Louis  456 1234567

          003     New York Branch   Phil Espos 212 5551234[/font][/quote]THANKS /b[/quote]I'm note sure I understand what you are trying to do BUT it occurs to me that it doesn't sound like the sort of thing that can be achieved using a database import approach.

           

          HOWEVER, if you were to export the Excel to a fixed width file you could treat the 'Regional Manager' line as an APPEND, include the R Manager name in each detail record and then use a calculated to field to If/Then/Else that I think you are seeking. But I could be wrong about what you need to achieve.

           

           

          Grant

           

          [size="1"][ July 14, 2005, 07:08 AM: Message edited by: Grant Perkins ][/size]

          • Calculated Field Referencing Previous Record
            Data Kruncher

            TPaul,

             

            From your description, it looks to me like you're trying to use the results of a pivot table (copied to another sheet as values) as the data source for Monarch.

             

            I don't think that there's a simple Monarch solution for this, so let's tackle the solution from within Excel. It's pretty simple to fill the empty spaces with data from the rows above, but it does come across as a bit of a fancy Excel trick.

             

            Supposing that your Branch No, Name, and Manager are in columns A, B, and C respectively, here are the steps:

            • Select columns A, B, and C

            • Type Ctrl-G or hit F5 to bring up the Go To dialog box

            • Click the Special... button

            • Click Blanks and click OK

            • Type + UP ARROW

            • Type Ctrl-Enter instead of just Enter to complete the formula

            • Select columns A, B, and C once more

            • Copy the selection and paste it back as values

             

            You now have a complete data set with which to work in either Excel or Monarch. Once you're familiar with this technique it only takes a few seconds to fill all those empty cells with the right data.

             

            Let us know if this resolves your problem.

             

            Kruncher