5 Replies Latest reply: May 15, 2014 9:52 AM by jane888 _ RSS

    Help on how to extract data

    jane888 _

      Hi, below is a report I rcv'd from a vendor and this is the only format I can get:

      [font="courier"]0      ******        ************       ***********************                                                                        

      NAME: ABCDE     ID: 111111111A    NUM: 0000000000                           FI CTRL:     11111111111111            BILL FREQ: 5       

              

                •        ************       ***********************           PAID DATE: 01/01/00 FROM DATE: 01/01/00 THRU DATE: 01/01/00  

      *T-REV CODE  UNT    CHARGES  FEE         ASC *T-REV CODE  UNT    CHARGES  FEE         ASC *T-REV CODE  UNT    CHARGES FEE         ASC  

      -


      -


      ---    -


        -


         - *--- -


      ---    -


        -


         - *--- -


      ---    -


      -


         ---  

      L-300 ATP03   1       1.00       1.11        L-300 83605   1      11.00      11.11        L-300 ATP03   1       1.11      1.11        

      L-300 ATP03   1       1.00       1.11        L-300 85027   1      11.00       1.11                                                    

      TYPE       CHARGES          REIM       CASH     BLOOD DED        COINS           MSP          OTL      INT        PRICE  ESRD / GAPC  

      -


             -


                -


             -


           -


              -


                 ---          ---      ---        -


        -


          

      Q-ATP        1.11-          1.11-                                                  /font[/quote]And the actual data should be like this:

      [font="courier"]0      ******        ************       ***********************                                                                        

      NAME: ABCDE     ID: 111111111A    NUM: 0000000000                           FI CTRL:     11111111111111            BILL FREQ: 5       

              

                •        ************       ***********************           PAID DATE: 01/01/00 FROM DATE: 01/01/00 THRU DATE: 01/01/00  

      *T-REV CODE  UNT    CHARGES  FEE         ASC TYPE       CHARGES          REIM       CASH     BLOOD DED        COINS           MSP          OTL      INT        PRICE  ESRD / GAPC   

      -


      -


      ---    -


        -


         --- -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      -


      L-300 ATP03   1       1.00       1.11       Q-ATP         1.11-         1.11-

      L-300 ATP03   1       1.00       1.11                                                            

      L-300 83605   1      11.00      11.11                                                                               

      L-300 85027   1      11.00       1.11                                                    

      L-300 ATP03   1       1.11       1.11         /font[/quote]I've tried Multiple Column Region and other ways but haven't solved it yet.  Any help is much appreciated.  Thanks a bunch in advance.

       

        :confused:

        • Help on how to extract data
          Grant Perkins

          Hi Jane,

           

          MCR looks like it should be capable of doing what you need with the T-REV type records but you will need to define where the MCR section starts and stops. That may mean defining some 'dummy' templates to work with to give you those start and stop lines.

           

          The "Type" "Charges" "REIM" section is another matter.

           

          It looks like you could set it up as a FOOTER template but if you do that the fields will appear for each of the records created by the MCR, not just the first line. Would that matter? Is there a direct connection between only the first line and the Type, etc. fields?

           

          What is your output target format at the end of this analysis?

           

           

          Grant

           

          [size="1"][ December 30, 2006, 01:29 PM: Message edited by: Grant Perkins ][/size]

          • Help on how to extract data
            Grant Perkins

            Jane,

             

            MCR will do the job. However the sample has a small anomaly with what will be the 3rd 'Column' where the "Charges" and "Fee" columns in the sample are only 1 space apart whereas in columns 1 and 2 they are 2 spaces apart. Whether this causes any problems for defining a good trap I don't know - maybe the real report does not have the problem?

             

            Anyway here's a suggestion.

             

            Define the Page Header template - looks like it may be worth having one here.

             

            Define a 3 line footer template for the TYPE CHARGES REIM section and map the field in there.

             

            Set the MCR on and define is as 3 columns, starting at position 1 and being 45 characters wide. Set the Boundaries to Start after top of page and Stop at Template, then select the Footer template from the list available.

             

            Now define your Detail template. Tick the 'Multi-column Design Mode' box to make what you are playing with a little clearer. Paint the fields you require as if the column is the full page width.

             

            If all has gone well when you complete that when look in the table window you should see the fields you require, although the footer fields info will appear in each record as I mentioned before.

             

            Does this get you nearer to a solution?

             

             

            Grant

            • Help on how to extract data
              jane888 _

              Dear Grant, Happy New Year! Thanks much for this useful suggestion! However, I have to apologize for the confusion caused by my ambiguous explanation in the original post.  The original report I rcv'd is like this:

               

              [font="courier"]

              0      ******        ************       ***********************                                                                        

              NAME: ABCDE     ID: 111111111A    NUM: 0000000000                           FI CTRL:     11111111111111            BILL FREQ: 5       

                      

                        •        ************       ***********************           PAID DATE: 01/01/00 FROM DATE: 01/01/00 THRU DATE: 01/01/00  

              *T-REV CODE  UNT    CHARGES  FEE         ASC *T-REV CODE  UNT    CHARGES  FEE         ASC *T-REV CODE  UNT    CHARGES FEE         ASC  

              *----- -


              ---    -


                -


                 - *--- -


              ---    -


                -


                 - *--- -


              ---    -


              -


                 ---  

              L-300 ATP03   1       1.00       1.11        L-300 83605   1      11.00      11.11        L-300 ATP03   1       1.11      1.11        

              L-300 ATP03   1       1.00       1.11        L-300 85027   1      11.00       1.11                                                    

              TYPE       CHARGES          REIM       CASH     BLOOD DED        COINS           MSP          OTL      INT        PRICE  ESRD / GAPC  

              -


                     -


                        -


                     -


                   -


                      -


                         ---          ---      ---        -


                -


                  

              Q-ATP        1.11-          1.11-

              /font[/quote]And the actual data should be like this:

               

              [font="courier"]

              NAME   ID         NUM            FI CTRL      BILL FREQ  PAID DATE   FROM DATE   THRU DATE  *T-REV  CODE     UNT   CHARGES   FEE  ASC  TYPE   CHARGES   REIM  CASH  BLOOD DED  COINS   MSP  OTL  INT PRICE ESRD / GAPC

              ABCDE  111111111A 0000000000  11111111111111          5  01/01/2000  01/01/2000  01/01/2000  L-300  ATP03       1         1  1.11      Q-ATP     -1.11  -1.11

              ABCDE  111111111A 0000000000  11111111111111          5  01/01/2000  01/01/2000  01/01/2000  L-300  ATP03       1         1  1.11

              ABCDE  111111111A 0000000000  11111111111111          5  01/01/2000  01/01/2000  01/01/2000  L-300  83605       1        11 11.11

              ABCDE  111111111A 0000000000  11111111111111          5  01/01/2000  01/01/2000  01/01/2000  L-300  85027       1        11  1.11

              ABCDE  111111111A 0000000000  11111111111111          5  01/01/2000  01/01/2000  01/01/2000  L-300  ATP03       1      1.11  1.11

               

              /font[/quote]The field "TYPE" and the fields after that are continuous of the record.  As you may have noticed, not all the "T-REV" necessarily have "TYPE" information.  

               

              Hopefully this cleared up the confusion.

               

              Thanks a bunch in advance.

              • Help on how to extract data
                Grant Perkins

                Hi Jane,

                 

                You can extract the leading fields you need from the Page Header template. Normal processing for a template  - just 'paint' the fields and define them and they will appear as if from an Append Template.

                 

                That's the easy bit ...

                 

                As for the final line  - there are other ways we could look at to get at the fields in there but unless we look at some multi step process you will always see the set of fields included for each detail record. (Just like the fields from the page header will be.)

                 

                The thing is that I can't see any way the report format indicates that the first line in the second 'part' of the report relates to the first line in the first column of the 'body'. We migh assume that of course but then what if there is another line in the second part of the report? Would it be safe to assume that it must relate to the second line of the first column in the main body - if there is one?

                 

                There is some potential for making the output you require in a Summary and then using the 'suppress duplicate values' feature BUT that would not be very likely to work with the output format you require - it tends to be useful for fields to the LEFT rather than the right of the summary table.

                 

                If you have the possibility of multiple lines in the second section of the report it may be wiser to think of the file as 2 reports. Extract the second section and make it into a lookup file by adding a suitable key field or group of fields.

                 

                Then run a process for the Detail and Page header fields extraction and add the lookup data to that using an External Lookup.

                 

                Whatever or whoever your report file source they are not giving you much here to make your analysis simple in any way! In fact quite the opposite.

                 

                 

                HTH.

                 

                 

                Grant

                • Help on how to extract data
                  jane888 _

                  Hi Grant, I completely agree with you that our vendor did make my job more complicated than it would be.  Unfortunately, they don't have anyone who can change the report format and they don't plan to do so, either.  I gotta to deal with this reality.  I'll give it a try with the Page Header template and external Lookup and let you know how it goes.

                   

                  Thanks much.