10 Replies Latest reply: May 15, 2014 10:08 AM by mdyoung _ RSS

    Capturing previous detail data

    mdyoung _

      I have a pretty basic report (sample below). There are no append rows..... everything is a detail line, except for the header row....obviously. My trap for the detail line is using the Date[/I] column. The only column that could wrap to a second line is the Message[/I], in which I have the End Field On[/I] option set to End of left Justification[/I]. Everything works fine.

       

      Now for the tricky part where I need a guru's assistance. I need help in creating a calculated field to determine how many minutes have passed since the previous detail line's time. All time is Military; however, no colons exists, but that's easy to place in there and convert to date/time format. I tried the ol' guru trap by defining the detail line again as an append in hopes to create a second time field and simply calculate the difference between the two, but that didn't work..... or I didn't do the guru trap properly.

       

      For example:[/B]

      In the sample below, there's a detail line where the time is 0349 and the previous detail's time is 0024. I need a calculated field that will give me a value representing 205 minutes have passed since the last event message.

       

      DATE:     TIME:  TYPE:      MESSAGE:                                                   

      -


            -


         -


             -


                                                            

      08/19/10  0004   CLOSE DAY  Auto Close Day posted batch RAD #324 08/18/10             

      08/19/10  0022   BATCHES    Auto Batch PHA.N.LIVE (#325) for 08/18/10: Retrieval      

                                  complete                                                  

      08/19/10  0022   BATCHES    Rejection Auto Batch PHA.N.LIVE has been created          

      08/19/10  0024   CLOSE DAY  Auto Close Day posted batch PHA #325 08/18/10             

      08/19/10  0349   CLOSE DAY  08/18/10 close waiting for ADM.N.LIVE Auto Batches        

      08/19/10  0349   BATCHES    Auto Batch ADM.N.LIVE (#326) for 08/18/10: Retrieval      

                                  complete                                                  

      08/19/10  0350   CLOSE DAY  Auto Close Day posted batch RC #326 08/18/10              

      08/19/10  0350   CLOSE DAY  Auto Close Day posted batch ERSUN #22 08/18/10            

      08/19/10  0350   CLOSE DAY  Auto Close Day posted batch ERSUN #56 08/18/10            

       

      /code

       

      I use Monarch 10.5 Pro and DataPump 10.5 Standard. Any suggestions on how to accomplish this task?

       

      Thanks,

       

      Micheal

        • Capturing previous detail data
          Joe Berry

          Hopefully, someone else will jump in here, but I do not think this can be done.  What I have had to do in similar situations is do the calculation in Excel.  Monarch has no way to reference the previous record. 

           

          I use Monarch to format the data correctly, so that once in Excel, I am able to enter a formula and have my calculated field.

            • Capturing previous detail data
              Data Kruncher

              It can't be done in a single pass, but it can be done in two passes.

               

              I named your time field ThisTime.

               

              In the first model, create a Numeric calculated field named RowNo with:

              RowNo()[/code]Create a project export to send the Table to a file. Save the model and the project, and run the project export.

               

              Now add another Numeric field named PreviousRow with:

              If(RowNo=1,1/0,RowNo-1)[/code]Add an external lookup (the only way to "connect" data from previous rows) using the previous project export as the data source. Connect PreviousRow to RowNo in the external file, and import the ThisTime field from the external file as PreviousTime in the current model.

               

              Now the numeric MinutesElapsed field needs:

              Val(Left(ThisTime,2))*60+

              Val(Right(ThisTime,2))-

              (Val(Left(,2))*60+Val(Right(,2)))[/code]Save the model.

               

               

              You may need to make an allowance for PreviousTime having a null value in the first record in this formula.

               

              I don't know how Data Pump handles multiple pass processes, but I do know that it can be done.

               

              How's that?