12 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Two line append field on consecutive detail lines

    Data Kruncher

      OK, I'm stumped.

       

      [font="courier"]    Market Area                Project No.  Project Name          Customer Name         Agreement No.    Funding Source   Dept No. Project Type              Revenue    Percent   

          ________________________   __________   ___________________   ___________________   ______________   ______________   _______  ______________   ________________  _________   

          aaa-aaaaaaaaaaaaa /         99999       xx xxxxxxx xxxx xxxx  xxxxx xxxx xxxxx xxx  xxxxx            xxxxxxxxxx-xxxx  9999     xxxx & xxxxxxxx        99,9999.99               

          bbbbbbbbbb                 *77777       xxxxx x xxx xxxxxx x  xxxx xxxxxxxxx xxxxx  99999            xxxxxxxxxx-xxxx  9999     xxx @ xxx xxxxx             99.99               

                                     *88888       xxx xxxxxx xxx xxxxx  xxxx xx xxxxxxxx      99999            xxxxx/xxxxx-xxx  9999     xxxx & xxxxxxxx   <         99.99>              

        /font[/quote]How do I get the full Market Sector text (a's & b's) onto each record?

       

      Everything to the right of, and including, the Project Number I've defined as a detail template.

       

      The Market Sector is an append template.

       

      Because some but not all of the Market Sector description are multi-line on the same line as new detail lines, I can't get this setup right no matter which variation of End Field I try.

       

      As always folks, your assistance is invaluable and much appreciated.

       

      Kruncher

        • Two line append field on consecutive detail lines
          Grant Perkins

          Er, yeess. Good challenge.

           

          The constraint seems to be the single line detail record which, in effect, constrains the Append to a single line as well.

           

          I'm thinking along the lines of re-formatting the report prior to processing but even that is not problem free.

           

          For example even to re-format the spearated line into a single line it would be necessary to always have at least 2 lines available as the sample for the template. So either 2 lines as per the posted example or one line and a blank line.

           

          In fact everything I can think of at the moemnt requires that 2 lianes are available.

           

          So then I'm thinking EITHER extract the Market Area code as a multi-line field AND the rest of the line as another multi-line field (based on a single detail template probably trapped somehow on the Market Area field) and then reformat or extend the width of the Market Are field, control the appearance of the other 'field' to maintain the rows format and then export that to create a revised input file OR;

           

          Use the first line of the 'rest of the record' for a sort of reversed lookup where the re-constituted Market Area code is added to the original report from an external look up and re-exported thus giving a revised version of the report to work with.

           

          There may be some variations on the theme depending on the data content and whether that can be used in some way that is not obvious from the de-personalised data.

           

          The first option seems more logical as there is only one step BUT I'm not sure how easy it would be to control the starts and ends (and so the published export format) of the 'rest of the line' field so that it looks as it did originally.

           

          Lost trailing spaces may be a problem?

           

          I'll try to get back to this later ...

           

          Grant

          • Two line append field on consecutive detail lines
            Nick Osdale-Popa

            Oy! This is [b]exactly[/b][/i] what I'm struggling with too!

            • Two line append field on consecutive detail lines
              Data Kruncher

              Thanks for the input Grant. (Edit: Nick, you posted while I was typing.  Will the approach below work for you too?)

               

              I've come up with a resolution to the problem, thinking along the lines of Nick's brute force solution to Ralphs SetRuntimeParameter problem yesterday (nice one, BTW) - come at it another way.

               

              I made a model to extract only the Market Areas, ending on Left Justification. I then summarized this and exported it to an xls file.

               

              Then I changed my original model to stop treating the Market Area as an append, and include it in the detail.

               

              A scan of the report (which included all Market Area names which will ever appear) revealed, quite fortunately, that all of the bbbbbbb items were unique and would therefore only ever apply to their associated aaaa items. So...

               

              I then edited the exported xls to add new rows for the bbbbbbb and aaaaa items, filling in the full Market Area names, and simply duplicating the one line names in the other column, as necessary.

               

              Finally, I added an external lookup to translate the one line Market Area field (be it aaaa or bbbb) to its full equivalent.

               

              Done.

              • Two line append field on consecutive detail lines
                Grant Perkins

                Sandy,

                 

                Nice that the bbb items were unique. Were the aaa items that had associated bbb items also unique as combinations? (I was not sure from my rather swift reading of your description.)

                 

                If so you could base the entire solution on a fixed (internal?) lookup table using just the aaa item (as an append even) as the key.

                 

                Which seems to be pretty much what you did.

                 

                Sometimes I guess we have to rely on the data content helping the solution along!

                 

                On the other hand does this problem maybe suggest an addition to the wish list? The potential for an append to have a number of lines in its 'sample' which are not constrained by a detail template but only by the number of lines OR another occurrence of itself?

                 

                 

                Grant

                • Two line append field on consecutive detail lines
                  Data Kruncher

                  You're correct Grant, it was fortunate that the aaaa's were also unique.

                   

                  You're also correct that this could all be handled with an internal lookup table. I just found it easier to edit externally.

                   

                  The other nicety in this otherwise troublesome piece was that the report does a page break on every new Market Area, so it never shows more than one on a page.

                   

                  This would indeed be a nice enhancement. v9.02?      

                  • Two line append field on consecutive detail lines
                    Grant Perkins

                    Hi Sandy,

                     

                    I fully agree about the external edit.

                     

                     

                    ... but then you could cut and paste the result into an internal lookup and (thinking V9) have it as an object to be linked to from other models  - should you ever need them.

                     

                    A little easier perhaps than linking to an external lookup since the table is not dynamic.

                     

                    Just thinking aloud and trying to bring life to some of the V9 possibilities.      

                     

                     

                    Grant

                    • Two line append field on consecutive detail lines
                      Nick Osdale-Popa

                      I'm trying to use an internal lookup, but I can't get it to ignore the second line.  Here's how my report is formatted:

                      [font="courier"]JOBNUMBER (ALPHA/NUMERIC var size)  5183X     21078.11     21050.21     21050.21        27.90        27.90        0.00      0.00 W   

                      JOBNAME IS HERE                     5187X    170244.42    170146.50    170146.50        97.92        97.92        0.00      0.00 W   

                                                          5606X       857.12       857.12       857.12         0.00         0.00        0.00      0.00 W   

                                                          8742       6307.68      6307.68      6307.68         0.00         0.00      110.40      0.00 W   

                                                          8810      15025.78     15025.78     15025.78         0.00         0.00      238.93      0.00 W   

                                                    ====================================================================================================   

                                                   JOB TOTALS      213513.11    213387.29    213387.29       125.82       125.82      349.33      0.00 W    /font[/quote]And here is what the table is showing:

                      [font="courier"]JOBNUMBER     LOOKUPJOBNAME     5183X     21078.11     21050.21     27.9     0

                      JOBNAME      -NOT FOUND-     5187X     170244.42     170146.5     97.92     0[/font][/quote]I was also going to do another lookup that would display the Job Number based on the name. The only problem is that some Job Numbers have the same Job Name!

                       

                      If I can get the trap to ignore the second line, I can make it work.

                      • Two line append field on consecutive detail lines
                        Grant Perkins

                        Nick,

                         

                        If you are adopting DK's 2 step process I would see the first step as the 'First Column' - Job Number and Job name.

                         

                        Is that always 2 rows?

                         

                        If so make the sample for this simple detail extraction a 2 row sample and you should have little problem with trapping of line 2 since it would be covered by the first trap's template. (I think, based on what you posted.)

                         

                        Does that make the lookup table (internal or external) any easier to work with or have I missed something else?

                         

                        Or are you talking about the second step detail record where a field or append (to may the connecting key) would be difficult because there is no obvious way to stop the second line being trapped? (I think you may well be looking at that difficulty rather than my first one.)

                         

                         

                        Grant

                        • Two line append field on consecutive detail lines
                          DavidS _

                          Hello everyone,

                           

                          I'm fairly new to this forum, although I've been using Monarch for many years. I've faced similar situations in the past. Here's what I've done (it requires a bit of programming):

                           

                          I take the input file, and create a new input file by adding either at the beginning or at the end of each line the absolute line count relative to the top of the file. I end up with a file that looks like this 300-line sample file:

                          [font="courier"] 

                             1  xxxxxx xx/xx/xx  9999.99

                             2  xxxxxx xx/xx/xx  9999.99

                             .

                             .

                             .

                          300  xxxxxx xx/xx/xx  9999.99[/font][/quote]I then create two separate models: one to capture the detail, including a field for the absolute line number, and a second one to capture the append (in this case, of course, as a detail record), also with the absolute line number as one of the fields. Once I have the two exports, I simply join the two resulting tables on the absolute line number.

                           

                          At some point, I tried to use the built-in Line and Page functions, but when you have a multi-line field, the functions return the page and line number of the last line; if they returned the page and line number of the first line, then I wouldn't have to create my own line numbers. Perhaps this option should be considered as a possible future enhancement.

                          • Two line append field on consecutive detail lines
                            Grant Perkins

                            Originally posted by DavidS:

                              Here's what I've done (it requires a bit of programming):

                             

                            /b[/quote]David,

                             

                            Are you using the programming to add the line number?

                             

                            It occurs to me this could also be done using Monarch (for those like me who "don't" program     ).

                             

                            Simply select the entire line of the report as a single field. Add the line number as a calculated field using the Line() function and then export (or print to a disk file) the result as a fixed width type file.

                             

                            Then run the 2 model process.

                             

                            Still looks a bit tricky though unless the report ALWAYS allows the template for the split line data to consist of 2 lines, even if the second line is empty.

                             

                            Or am I overcomplicating this?

                             

                             

                            Grant

                            • Two line append field on consecutive detail lines
                              DavidS _

                              Grant,

                               

                              Thanks for the suggestion. There is just one twist: the function to use should be Recno(), which returns the unique record number, instead of Line(), which returns to line number relative to the top of the page. Otherwise, it works great.

                               

                              David

                              • Two line append field on consecutive detail lines
                                Grant Perkins

                                Originally posted by DavidS:

                                Grant,

                                 

                                Thanks for the suggestion. There is just one twist: the function to use should be Recno(), which returns the unique record number, instead of Line(), which returns to line number relative to the top of the page. Otherwise, it works great.

                                 

                                David /b[/quote]David, thanks for pointing that out - I should have remembered that Page() needed to part of it as well.

                                 

                                The only downside with RECNO(), though it will not apply in this case I guess, is that it relates to the record in the table rather than the line of the report. You probably know that anyway but it may be worth making the point for those who read this weeks or months from now. Of course if one is picking every line anyway that relationship will be one to one so no problem.

                                 

                                Sounds like it has been a useful discussion for both of us, which is good.

                                 

                                Grant