9 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Mutiple line, mutiple floating traps

    Andrew.holden _

      Commissioning Serial Number,Code of GP (Antenatal Care)

                                                                     Episodic Address,Episodic Postcode,Registered GMP Code

                                                                     Referrer Code,Referring Organisation Code,

       

      There can be any combination of items, of different lengths and different number of rows.  Sometimes the end one has a comma sometimes not.  the information is appended to some header detail.  I have tried creating floating traps to capture the data but i need each item to be in an individual row.

       

      Thanks for any help.

        • Mutiple line, mutiple floating traps
          henryiriawan _

          Hi Andrew,

           

          Welcome to Monarch Forum

           

          Could you give us a sample of your reports that you want to parse on, so we can better help you out? You can eliminate the confidential information...

            • Mutiple line, mutiple floating traps
              Grant Perkins

              Hi Andrew,

               

              Is your location Manchester UK or another Manchester?

               

              From your description I am thinking you have a multi-field record in a text file which is probably based on a CSV file but the lines are wrapped from theior original very wide format into something that will 'print' on narrow paper or screens.

               

              If it IS a CSV file then hopefully all the fields will exist comma separated even when empty and will be as described by the header 'example'.

               

              If that is the case your best bet may be to look to the Monarch Utility (assuming you are using V8 or V9, otherwise one of the separate utility programs provided with earlier versions) and modify the file format to make it more suitable to work with. Most likely you will converting it back to the form in which it was originally produced!

               

              Once the record is back to a single row your (carefully constructed)  floating trap would probably work well but better, if you have the Pro version of Monarch, would be to simply read the resulting file as a database .

               

              Of course my assumptions here could be wrong and we may find ourselves heading off in a different direction in search of your solution. But this seems like a good place to start ...

               

              HTH.

               

               

              Grant

                • Mutiple line, mutiple floating traps
                  Andrew.holden _

                  .....District.No...Surname.....................CDS....Update...Start.Date.........End.Date..........Activity.Date

                  ...............................................Type...Type.....Data.Items.in.Error

                  .....--


                  ...
                  ...
                  ...--...--


                   

                  .....RXR111111....AAAAAAAA,.A.................160....9........n/a................n/a...............28/11/2007.18:15

                  ...............................................................Commissioning.Serial.Number,Code.of.GP.(Antenatal.Care)

                  ...............................................................Episodic.Address,Episodic.Postcode,Registered.GMP.Code

                  ...............................................................Referrer.Code,Referring.Organisation.Code,

                   

                  I have added the dots as i could not get the format correct.

                  In the original the headings are all lined up.

                   

                  Thanks for your help so far.  What i need is for each error description i need 1 row in a database, so for the above i need something like this:

                   

                  RXR111111,"AAAAAAA,A" ,160,9,"Commissioning.Serial.Number"

                  RXR111111,"AAAAAAAA,A",160,9,"Code.of.GP.(Antenatal.Care)"

                  RXR111111,"AAAAAAAA,A",160,9,"Episodic.Address"

                  RXR111111,"AAAAAAAA,A",160,9,"Episodic.Postcode"

                  RXR111111,"AAAAAAAA,A",160,9,"Registered.GMP.Code"

                  RXR111111,"AAAAAAAA,A",160,9,"Referrer.Code"

                  RXR111111,"AAAAAAAA,A",160,9,"Referring.Organisation.Code"

                   

                  I have created the above but i can only either select the complete line or using the floating trap select the 1st error in a row.

                   

                  Thanks for any further help.

                  Andy

                    • Mutiple line, mutiple floating traps
                      henryiriawan _

                      Hi Andrew,

                       

                       

                      I have added the dots as i could not get the format correct.

                      In the original the headings are all lined up.

                      /quote

                       

                      Please see this thread to make your report looked good when you posted in here: http://www.monarchforums.com/showthread.php?t=2290[/url]

                      • Mutiple line, mutiple floating traps
                        Grant Perkins

                        [code].....District.No...Surname.....................CDS....Update...Start.Date.........End.Date..........Activity.Date

                        ...............................................Type...Type.....Data.Items.in.Error

                        .....--


                        ...
                        ...
                        ...--...--


                         

                        .....RXR111111....AAAAAAAA,.A.................160....9........n/a................n/a...............28/11/2007.18:15

                        ...............................................................Commissioning.Serial.Number,Code.of.GP.(Antenatal.Care)

                        ...............................................................Episodic.Address,Episodic.Postcode,Registered.GMP.Code

                        ...............................................................Referrer.Code,Referring.Organisation.Code,

                        /code

                        I have added the dots as i could not get the format correct.

                        In the original the headings are all lined up.

                         

                        Thanks for your help so far. What i need is for each error description i need 1 row in a database, so for the above i need something like this:

                         

                        RXR111111,"AAAAAAA,A" ,160,9,"Commissioning.Serial.Number"

                        RXR111111,"AAAAAAAA,A",160,9,"Code.of.GP.(Antenatal.Care)"

                        RXR111111,"AAAAAAAA,A",160,9,"Episodic.Address"

                        RXR111111,"AAAAAAAA,A",160,9,"Episodic.Postcode"

                        RXR111111,"AAAAAAAA,A",160,9,"Registered.GMP.Code"

                        RXR111111,"AAAAAAAA,A",160,9,"Referrer.Code"

                        RXR111111,"AAAAAAAA,A",160,9,"Referring.Organisation.Code"

                         

                        I have created the above but i can only either select the complete line or using the floating trap select the 1st error in a row.

                         

                        Thanks for any further help.

                        Andy[/quote]

                         

                        Is this how it looks?

                         

                         

                        Grant

                          • Mutiple line, mutiple floating traps
                            Grant Perkins

                            Andrew,

                             

                            I have been wondering of the simplest approach would be to use the Monarch Utility (or some other tool of your choice) to re-arrange the input file before processing.

                             

                            For example one might consider processing it to convert all commas to CRLF combinations. (Potential drawback with the name are but it may not be a problem) .

                             

                            That would put each error identifier on its own line. Those lines would then become detail lines, easily templated, and everything above could be appends. You would get the output you require.

                             

                            Something similar could be achieved using Monarch in a 2 stage process (scripted into a single activity using a batch file) to create an intermediate file to firstly create a more usable export file and then a working table from that.  I have some thought that might create an output that would allow the use of MCR functionality to separate the record into the form you need.

                             

                            Let us know if this is still a problem for you.

                             

                            Grant

                              • Mutiple line, mutiple floating traps
                                Grant Perkins

                                Had a little play with the 2 step process and produced this.

                                 

                                District     Name              eg_160  eg_9  eg_n/a  A    Date          Time        Error

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Commissioning.Serial.Number

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Episodic.Address

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Referrer.Code

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Code.of.GP.(Antenatal.Care)

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Episodic.Postcode

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Referring.Organisation.Code

                                RXR111111    AAAAAAAA,.A....    160    9    n/a    n/a    2007-11-28    18:15:00    Registered.GMP.Code

                                 

                                /code

                                 

                                I used a 2 step process all done in Monarch. Each part is quite simple.

                                 

                                I can provide details if required.

                                 

                                 

                                Grant

                                  • Mutiple line, mutiple floating traps
                                    Andrew.holden _

                                    Grant

                                    Thats exactly what i am looking for please provide details.

                                     

                                    Thanks for the tip on using the code tags, below is a raw report and what i have achived so far.

                                     

                                    Thanks

                                    Andy

                                     

                                      aaaaaaaaaaaaaaa HOSPITALS NHS TRUST             CDS Extract Error Report                Run Date : 26/01/2008 01:00    Page :1

                                     

                                         Errors From       : 28/11/2007                        

                                         CDS Group         : INPATIENT                          Provider          : D1

                                         Hospital Code(s)  : ALL                                Specialty Code(s) : ALL

                                         CDS Type          : ALL                                Error Group       : ALL

                                     

                                         Error logged   :   11/12/2007 01:18          Related From Date :   16/09/2007        To Date :   10/12/2007

                                         Provider Code  :   D1

                                         Hospital Code  :  

                                     

                                         District No   Surname                     CDS    Update   Start Date         End Date          Activity Date

                                                                                   Type   Type     Data Items in Error

                                         -


                                       -


                                       -


                                       -


                                       -


                                     

                                         RXR0919496    aaaaaaaa, a                 160    9        n/a                n/a               28/11/2007 18:15

                                                                                                   Commissioning Serial Number,Code of GP (Antenatal Care)

                                                                                                   Episodic Address,Episodic Postcode,Registered GMP Code

                                                                                                   Referrer Code,Referring Organisation Code,

                                     

                                         Total number of episodes =   1

                                       aaaaaaaaaaaaaaa HOSPITALS NHS TRUST             CDS Extract Error Report                Run Date : 26/01/2008 01:00    Page :2

                                     

                                         Errors From       : 28/11/2007                        

                                         CDS Group         : INPATIENT                          Provider          : D1

                                         Hospital Code(s)  : ALL                                Specialty Code(s) : ALL

                                         CDS Type          : ALL                                Error Group       : ALL

                                     

                                         Error logged   :   11/12/2007 01:18          Related From Date :   16/09/2007        To Date :   10/12/2007

                                         Provider Code  :   D1

                                         Hospital Code  :   BGH

                                         Specialty      :  

                                     

                                         District No   Surname                     CDS    Update   Start Date         End Date          Activity Date

                                                                                   Type   Type     Data Items in Error

                                         -


                                       -


                                       -


                                       -


                                       -


                                     

                                         RXR0919496    aaaaaaaa, a                 150    9        n/a                n/a               28/11/2007 18:15

                                                                                                   Org Code (of Commissioner),Commissioning Serial Number,

                                     

                                         RXR1149850    bbbb, bb                    160    9        n/a                n/a               01/12/2007 06:20

                                                                                                   Commissioning Serial Number,

                                     

                                                                                   150    9        n/a                n/a               01/12/2007 06:20

                                                                                                   Org Code (of Commissioner),Commissioning Serial Number,

                                     

                                         RXR3093647    ccccc, c                    150    9        n/a                n/a               03/12/2007 18:41

                                                                                                   Org Code (of Commissioner),Commissioning Serial Number,

                                     

                                         RXR3254929    dddddddd, d                 150    9        n/a                n/a               19/09/2007 15:33

                                                                                                   Org Code (of Commissioner),Commissioning Serial Number,

                                     

                                         Total number of episodes =   5[/CODE]

                                     

                                    RunDate     ErrorsFrom     ErrorLogged     RelatedFromDate     RelatedToDate     Errors     DistrictNumber     Name     CdsType     UpdateType     StartDate     EndDate     ActivityDate

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Commissioning Serial Number,Code of GP (Antenatal Care)     RXR0919496     a     160     9     n/a     n/a     28/11/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Episodic Address,Episodic Postcode,Registered GMP Code     RXR0919496     a     160     9     n/a     n/a     28/11/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Referrer Code,Referring Organisation Code,     RXR0919496     a     160     9     n/a     n/a     28/11/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Org Code (of Commissioner),Commissioning Serial Number,     RXR0919496     a     150     9     n/a     n/a     28/11/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Commissioning Serial Number,     RXR1149850     b     160     9     n/a     n/a     01/12/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Org Code (of Commissioner),Commissioning Serial Number,     RXR1149850     b     150     9     n/a     n/a     01/12/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Org Code (of Commissioner),Commissioning Serial Number,     RXR3093647     c     150     9     n/a     n/a     03/12/2007

                                    26/01/2008     28/11/2007     11/12/2007     16/09/2007     10/12/2007     Org Code (of Commissioner),Commissioning Serial Number,     RXR3254929     d     150     9     n/a     n/a     19/09/2007

                                    /CODE

                                      • Mutiple line, mutiple floating traps
                                        Grant Perkins

                                        Andrew,

                                         

                                        I used a 2 step process the first step of which looks pretty much like where you have got to assuming that your error codes are all in a field called "Errors" as seen in your field title row. (I don't have all the field you have of course but that is not important for the extra step you need.)

                                         

                                        From that point I added some calculated fields to split the error text string into one error per field. Use the LSPLIT() function and create as many fields as you need for the max possible number of errors on a single line. Extra fields are not a problem. Names ErrorCol1, Errorcol2, etc will be fine. Make them wide enough for the max description length and positioned at the right of the table.

                                         

                                        Set font to FIXEDSYS or similar and export the table as a fixed width text file. You may need to set the 'printer' for a generic file only device to avoid formatting issues with large numbers of fields.

                                         

                                        The new output becomes and input file for step 2. where we will treat the new ErrorCol fields as columns using Multi Column  Regions.

                                         

                                        Define a second model with a template to capture the output - the detail will be based on the ErrorCol entries. Select the required fields from the data to the left of the columns as well. You will get a fully populated row for each field in each column. Some may be blank of course so a filter to select only populated entries will be required.

                                         

                                        Once the process is developed and tested you could easily package it into a single kick off batch file to make it easy to run in an automated fashion. Or even consider this and other standard requirements, which I am confident you will have based on my previous knowledge of NHS workings, as a candidate for very complete automation using Data Pump.

                                         

                                        If you send me a PM with an email address that will accept attachments I can send you my sample 'reports' and models to make understanding easier.

                                         

                                        There may be other approaches to this but I think keeping each step simple yet recognising the power of multiple steps makes sense for development and maintenance at this point. It is also a re-usable technique that can work happily with multiple levels of data manipulation.

                                         

                                        HTH.

                                         

                                         

                                        Grant