5 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Irregular Data

    PDehner _

      I have a report that I need to accumlate date from insconsistant lines

      1 Record on report

      -


      9999999__SS#__Lname__Fname__Gndr_DOB

      Fatal__CODE__Description

      8888#77777____00000006666__5555555__-_ServDate

      Fatal__CODE__Description

      -


      Other records on report

      -


      444444444__333_00000006666_9999999__-00_ServDate

      Fatal__CODE__Description

      -


       

      I need to pull the Fatal_Code_Description - which is the only consistant line, which I do first but trying to build an append seems to be the real issue.

      I need the seven digit field repesented by the 999999 - The 4 digit 6666 and the ServDate

      and if the first line with SS#_Lname_Fname_Gndr_DOB appears include that - If not leave it blank.

       

      I get a mess depending on where and when the first line appears next to the non 4 line entry

      And always the table puts information in the SS#_Lname etc.. fields - from the previous record - although in the field definition I have to it to leave it blank.

       

      I have seen some posts that refer to the if statement - where and how do I put that in.

       

      Hope someone can help - I seem to be clueless.

      Thanks in advance

        • Irregular Data
          Grant Perkins

          Hi,

           

          Just want to make sure that I have understood this correctly.

           

          You report has various records, some you want, some you don't. All are 2 lines long with the consistent (and therefore trappable) line being the second line of a 2 line record.

           

          Is that about it?

           

          If so I think I would approach this as a single detailed record trapped on line 2 and picking the fields from line 2 that are consistent.

           

          For line one you may have 2 choices (possibly more). It is difficult to be certain of the best approach without experimenting with the report but here are a couple of ideas.

           

          If you know where the fields you need should be across the row (assuming they are consistently positioned) then just 'paint' the fields where you know they should be. Don't worry about whether you need them or not at this time.

           

          Go to the table view and see what you have. All being well you should have a mix of records with the specific fields that ID the ones you want correctly filled and the records you don't want with other 'garbage' in them. It should now be possible to work out a good FILTER definition that retains the record you want and eliminates the others based on the values in one or more of the fields in the table. That is where the IF function MAY come into play.

           

          As an alternative, if the fields in the variable line are not consistently placed for example or where there may be records required with different field positions which 'overlap' if you try to define them in a single template, you might consider simply grabbing the entire line as a single field and then using some calcualted fields to extract the data as separate fields using the IF function as part of the formula that defines how to deal with the field contents.

           

          Once that is complete you can use a filter process once again to exclude the records you don't need.

           

          (If you choose to go with the detail line and an append for the line above it you can do the same thing in both cases. However the append may produce some odd results unless each detail line has at least one associated line above it and that is the line from which you need to extract the fields.)

           

          This probably sounds complicated but is in fact easier to do than to describe!

           

          Have a go at checking the ideas and see where it takes you. If you get stuck (or if you don't even get started!) post back here and we can have a look at providing some more specific assistance.

           

          I hope this helps you move forward.

           

           

          Grant

          • Irregular Data
            PDehner _

            Thanks for the response but I believe you mis-understood the problem.

            Not all records on the report are 2 lines long.  Some are 4 lines and others are two.

            I have noted in my first post the actual records divided by -


             

            Hope now you see my dilema.

            Thanks

            • Irregular Data
              Grant Perkins

              Originally posted by PDehner:

              Thanks for the response but I believe you mis-understood the problem.

              Not all records on the report are 2 lines long.  Some are 4 lines and others are two.

              I have noted in my first post the actual records divided by -


               

              Hope now you see my dilema.

              Thanks /b[/quote]I did wonder at the time, which is why I paraphrased how I understood the problem.  But you did mention "the Fatal_Code_Description - which is the only consistant line," which I took to imply that the first 4 lines represented 2 records each with a different first line, one of which you wanted to get data from and the other you didn't.

               

              In terms of creating a model if you have a single record with either one or two instances of a specific line type it probably means that the templates will need to be designed to work around that consistency since it makes the templates potentially inconsistent. If you see what I mean.

               

              If the Fatal_Code_Description is the unique detail but some records have 2 entries, how do you identify which one to use? Or are you thinking of the code as the key that links lines 1 and 3 of the 4 line records?

               

               

              Grant

              • Irregular Data
                PDehner _

                Actually the code is the consistant part - but the information is related to one record.

                 

                For Example in the example  in the first record (4) Lines I need the following information:

                 

                9999999 Lname Fname Gndr DOB and 00000006666 ServDate

                 

                relate to the tow (2) Fatal Code_Description

                 

                (Actually two records but they relate to the same 999999 Lname Fname Gndr DOB and 00000006666 ServDate

                 

                In the second two line on the report I need

                00000006666 9999999 _ServDate related to the

                Fatal Code_Description

                 

                1 record one Fatal Code.

                 

                In the report not all are consistant - as in 4 line then two line - they are mixed with no rhymn or reason.

                 

                Any Clues???

                • Irregular Data
                  Grant Perkins

                  Originally posted by PDehner:

                  In the report not all are consistant - as in 4 line then two line - they are mixed with no rhymn or reason.

                   

                  /b[/quote]If there really is no identifiable pattern I think you are in difficult territory anyway.

                   

                  If necessary you could strip out the different 2 line combinations and build some summaries but I cannot see how you could put them back together again unless there are some clear rules or transaction connections somewhere in the report structure.

                   

                  Monarch provides a large number of tools to turn all sorts of 'reports' back into data in single or multiple passes. And you can potentially use it to reconstruct the format or line order of an entire report to make it easier to perform the final analysis.

                   

                  But if you cannot see a clearly definable relationship structure in the original report I cannot see how you could work out what to do to re-engineer it and yet be sure to get a result that is meaningful.

                   

                  For example, how do you know that the lines in the 4 line records sample do indeed go together or that the 2 line sample has no other fields related to it?

                   

                  If you know the rules for that situation then you do have at least something to work with. There's no guarantee that it will lead you to the result you need but it does offer some hope.

                   

                  I can read and understand what you say about it but really all I can see is something that looks like 3 separate records, of different formats, 2 of which might be joined in some way - perhaps the Fault code? But then maybe the fault code would also join a lot of other records as well.

                   

                  What is unique in the data so make the first sample identify itself as a 4 line record?

                   

                   

                  Grant