6 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Print files with inconsistent no. of lines

    Rosie9621 _

      Is Monarch capable of extracting data from print files when there is an inconsistent number of lines?

        • Print files with inconsistent no. of lines
          Grant Perkins

          Originally posted by Rosie9621:

          Is Monarch capable of extracting data from print files when there is an inconsistent number of lines? /b[/quote]If you mean what I think you mean, then yes.

           

          Hope this helps.

           

          Grant

          • Print files with inconsistent no. of lines
            Rosie9621 _

            Does the manual or tutorial explain this.  I have looked and cannot find it.

            • Print files with inconsistent no. of lines
              Rosie9621 _

              I did read that section.  That seems to be a field that requires more than one line to include all the data.  I have a line that is not present for every invoice.  A regular invoice would look like this:

               

              Invc#    Date    Customer Name

                  Gross Amt

                  Net Amt

                  Taxable Amt

                  Tax Amt

               

              Sometimes however, I get one that looks like this:

               

              Invc#    Date    Customer Name

                  Gross Amt

                  Net Amt

                  Non-Taxable Amt  

                  Taxable Amt

                  Tax Amt

               

              I can caputre the Non-Taxable Amt, but then it adds a non-taxable amount (repeated amount) on every invoice until it gets to another invoice with a non-taxable amount.  It adds this amount to invoices that did not have a Non-Taxable amount. 

               

              Will Monarch handle this situation?

              • Print files with inconsistent no. of lines
                Grant Perkins

                What you have is a situation where records usually have at least 4 lines but sometimes a fifth line is included for the non-taxable amount. Is that correct? Can there be other combinations of numbers of lines?

                 

                Do the value lines have tags preceding the values?

                 

                Invc# Date Customer Name

                Gross Amt:  9,999,999.99

                Net Amt: 9,999,999.99

                Non-Taxable Amt: 9,999,999.99

                Taxable Amt: 9,999,999.99

                Tax Amt: 9,999,999.99

                 

                for example?

                 

                I assume there must be something at the line level rather than a list of possible contents in a header description somewhere.

                 

                If so you have a couple of approaches (at least!) you could take.

                 

                One would be to treat each value line as a detail record and record it as value plus the description of the line. Add the Invoice #, date and Customer name as fields from an append.

                 

                The other approach would be to define a template with at least 5 lines in it (or more IF there can be more fields and IF you have enough lines to play with in EVERY RECORD between the start of one template and the start of the next template, as m,any lines as are available) and then paint in a field in each row and, using the ADVANCED tab in the field definition, define that the field has a PRECEDING STRING if it is to exist.

                 

                So if you can have up to 5 rows, but sometimes only 4, so long as you have 5 rows available in each template sample space to define a field in each you can make the preceding strings

                 

                 

                Gross Amt

                Net Amt

                Non-Taxable Amt

                Taxable Amt

                Tax Amt

                 

                and the fields will ONLY BY POPULATED in the table when they exist.

                 

                A third approach would be to take the entire block as a multi-line field, with a variable number of lines, and then 'slice and dice' it into its component values using a set of Calculated fields.

                 

                Which of these (or possibly other) approaches is most suitable will depend rather on the specific layout and content of the report.

                 

                Can you post a representative sample of the report(disguised data if necessary but don't disguise anything that might be useful from the headings and tags!)  so that we can have a look and make a more specific recommendation? Sometimes there are so many possibilities that it can be overly confusing to try to describe them all!

                 

                Grant

                • Print files with inconsistent no. of lines
                  RalphB _

                  Check out this  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000258#000002"]post[/url]  for a variation of Grant's solution.

                   

                  I have used a variation of tcorley's solution successfully on a couple of occassions.

                   

                  Ralph

                   

                  [size="1"][ May 01, 2006, 04:58 PM: Message edited by: Todd Niemi ][/size]

                  • Print files with inconsistent no. of lines
                    Grant Perkins

                    Originally posted by RalphB:

                    Check out this  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000258#000002"]post[/url]  for a variation of Grant's solution.

                     

                    /b[/quote]Well recalled Ralph!

                     

                    This is the same preceding string solution I outlined and does work well providing you know the constraints contained in the original report.

                     

                    If the smallest record in your report contains, say, the header line, 2 variants of the Numeric Amount lines and a space line before the next record starts, then you have only 3 lines (Possibly 4 if you can also use the header line) in which to define the preceding string fields you need. Great if you only have 3 (or 4) possible line 'types' but not so good if there are more.  (unless there are more field s but they do not overlap in the same character columns, in which case it may be possible to get away with it ...)

                     

                    So it, in the entire report, there might be, say, 8 different types of value field and they occupy the same horizontal position on the line, then you would need 8 free lines in the template sample space to define the fields.

                     

                    If you make the template 8 lines long you will have problems with missing records since the template will cover the start of the next record where any records do not have 8 rows - which would probably be all of them!

                     

                    So take care and watch for missing records during the template development phase.

                     

                    If this leaves you with a problem consider defining2 (or more) templates (appends in this case of course) with the same trap but with field definitions which will locate the additional fields that could not be defined in the first template. If that still leaves you with a problem of more fields - try a third template. And so on.

                     

                    I hope this additional information will be helpful.

                     

                    Grant

                     

                    [size="1"][ May 03, 2006, 05:04 PM: Message edited by: Todd Niemi ][/size]