8 Replies Latest reply: May 15, 2014 9:54 AM by GrantB _ RSS

    Inconsistent sort levels

    GrantB _

      I'm having trouble pulling data from a report that has multiple sort levels but the same fields are not presented in each level.  For example, the first level has Field 1, Field 2, and Field 3.  The second level has Field, 1, Field 3, but not Field 2.  The table view still pulls Field 2 for my second level and populates it with the information from Field 2 in the first level.  This is incorrect as Field 2 does not exist at this level.  How do you set up the template so that Monarch doesn't automatically read the data from the previous level?

        • Inconsistent sort levels
          Tom Whiteside

          Am unclear as to your report structure.  Does your second level still have an empty space for the missing Field 2?  Or, do your Fields have variable positions within each sort level?

           

          In general, if you double-click on a field in table view and bring up its Field Properties, then click on the General tab and look at Copy Field Values at the bottom, you have the choice to either "Fill empty cells with value from previous record," or to "Leave empty cells blank."

           

          If your model template is trapping the relative positions of your fields correctly, then try the "Leave empty cells blank" option.  Otherwise, let us know more about your report structure.

           

          Good luck!

          • Inconsistent sort levels
            GrantB _

            My second level does not have an empty space for the missing Field 2.  Instead of Field 2 in the same position, another field specific to that particular record is in its place.  Monarch apparently sees that Field 2 is missing and then fills in the same data from the previous field.  I had already checked the "leave empty cells blank" option but I don't think it applies in this case since the field is not empty.  Thanks for your help and let me know if I can provide some more detail that may make the issue more understandable!

            • Inconsistent sort levels
              GrantB _

              I've pulled some information out of the user's guide that may help to clarify my issue.  "Fields extracted using an append template are associated with the detail record if the append level begins anywhere on or before the first detail line.  The extracted field values are appended to all subsequent detail records until the next occurrence of the append field in the report."  My issue is that the next detail line in my report may not have one of the fields in the append template associated with that particular line of detail.  This is the way it should be and I don't want Monarch to append a template field to detail line to which it isn't associated.  Is there any way to override this functionality? 

               

              If this is not possible, the other way I can see making this work is by flagging each separate sort level in my report.  There is a line that I can trap to signify the start of a new level.  If there is some way to tell Monarch that everytime it sees a specific trap, it needs to start a new record, this would also solve my issue.

               

              Any thoughts?  Thanks!

              • Inconsistent sort levels
                Grant Perkins

                Hi Grant,

                 

                I'm trying to picture how your report looks. Sounds like you have a common detail line for both level1 and level2 but something above those in the sub-header of the report is different between the levels and you are getting to that data from different append templates. Is that about right? If so you will get some odd results - possibly from both appends though sometimes that may not be obvious.

                 

                One way around the problem might be to use a calculated field rather than the original problem field. To work that you would need to be able to identify data in another field that tells you whether your 'detail' is for level 1 or level 2. The logic would be something like

                 

                "If this is level 1 then use the data, else make the field blank." You would then sort and report on the calculated field rather than original field.

                 

                If none of you existing data fields can act as an indicator of level 1 or level 2 you may need to introduce an extra 'field' to be used only as part of the level identification process.

                 

                It all sounds more complicated than it really is. But I may have mis-guessed what you are working with. Is there any chance of posting an example of the layout/report structure from which we can offer a more definitive answer?

                 

                It may also be useful to know which version of Monarch you are using to avoid anyone offering a solution using functionality you don't have.

                 

                Regards,

                 

                Grant P

                 

                Originally posted by GrantB:

                I've pulled some information out of the user's guide that may help to clarify my issue.  "Fields extracted using an append template are associated with the detail record if the append level begins anywhere on or before the first detail line.  The extracted field values are appended to all subsequent detail records until the next occurrence of the append field in the report."  My issue is that the next detail line in my report may not have one of the fields in the append template associated with that particular line of detail.  This is the way it should be and I don't want Monarch to append a template field to detail line to which it isn't associated.  Is there any way to override this functionality? 

                 

                If this is not possible, the other way I can see making this work is by flagging each separate sort level in my report.  There is a line that I can trap to signify the start of a new level.  If there is some way to tell Monarch that everytime it sees a specific trap, it needs to start a new record, this would also solve my issue.

                 

                Any thoughts?  Thanks! [/b][/quote]

                • Inconsistent sort levels
                  Tom Whiteside

                  GrantB,

                   

                  Still can't get a clear picture of what your report structure involves.     :confused:     Once in a great while, changing a detail field to an append field and then trapping a more specific field as detail may help.

                   

                  I'd like to send you a separate private message with a request for a copy of your report, but you don't have your private message feature activated.  If you would contact me directly, I can assure of you of your data security and privacy.

                   

                  [size="1"][ April 16, 2003, 03:03 PM: Message edited by: Tom Whiteside ][/size]

                  • Inconsistent sort levels
                    GrantB _

                    Tom & Grant,

                    Thanks for your help and suggestions.  Tom, I think you're on the right track as far as understanding my issue.  I'm a new Monarch user so maybe the calculated field with some logic is the correct solution.  I've posted a sample of my problem data below.  The data didn't paste with the same format it actually has but I don't think that will affect the solution.  Tom, I'll send you an e-mail directly with the data in a better format.  Grant, if it helps I can send an e-mail directly to you as well.

                     

                    Some brief explanation - each distinct section of data is separated by the line that has Userfield1 and Userfield2.  You can see the first level of data lists a head office and associated delivery terms.  The second level of data has no head office and no delivery terms.  This is because these prices are not specifically associated with any head office - they are intended to stand independently.  Currently, my table is automatically filling in the head office and delivery terms information in the second level of data with the information from the first record even though these fields do not apply to this level.  I want the table to leave these fields blank on my second line in the table.

                     

                    XX/XX/2003                         REPORT                                     2

                    -


                     

                    Selling company              28

                    Head office                  36083          CUSTOMER NAME

                    Delivery terms               1211                EXW

                    Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                     

                                                             Valid from 11/11/2002 to 12/31/2003

                        999.99  USD   PER    100 KG FROM      750.000  KG    TT BA

                     

                    Userfield1 : NAME  Userfield2 : NAME

                     

                    Selling company              28

                    Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                    Plant                             E1

                     

                                                             Valid from 11/11/2002 to 12/31/2020

                        999.99  USD   PER    100 KG FROM            1.000  KG    TT BA

                        999.99  USD   PER    100 KG FROM   11,250.000  KG    TT BA

                        999.99  USD   PER    100 KG FROM   22,500.000  KG    TT BA

                     

                    Userfield1 : NAME  Userfield2 : NAME      Userfield3 : LIST

                     

                    Selling company              28

                    Head office                  586203         CUSTOMER NAME

                    Delivery terms               1211                EXW

                    Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                     

                                                             Valid from 11/11/2002 to 12/31/2003

                        999.99  USD   PER    100 KG FROM      750.000  KG    TT BA

                     

                    Userfield1 : NAME  Userfield2 : NAME

                    • Inconsistent sort levels
                      Grant Perkins

                      Hi Grant,

                       

                      Based on your posted report format (and assuming that certain fields are aligned on the real report, eg the qty weight) I have a model that will extract everything that looks to me like fields of data. However I am not sure what you need to extract as the base detail data. I have also assumed that where you have shown "Userfield x" in the report there is in fact a usable field name prompt before the field contents. If that is not the case we may need to look for a different solution for some parts of the model.

                       

                      So here it is;

                       

                      DETAIL line assumed to be the 999.99 USD per weight lines. Simple trap.

                       

                      I then set up Appends Templates for;

                       

                      Valid Start and End Date line;

                      Material Number Line.

                       

                      Both simple traps.

                       

                      I added another Append template for Selling Company and based it on the multiple lines for a specific Head Office.

                       

                      Trap on the TEXT 'Selling Price' and make it a 3 line trap (or whatever you require.

                       

                      Define a field for the Selling Co Number.

                      Define a field for the Customer Number and Name and make use of the OPTIONS> 'Start Field On' function "Preceeding String" and use the Text from the report (Head Office on the sample).

                       

                      You can do the same for Delivery Terms if you need that as a field. Also for 'Plant'from the other form of report section. Simply create a field in the appropriate place on the line and use the Preceeding String feature.

                       

                      Finally, based on your description of the Userfield line as a record delineator, I set up a FOOTER line (and I don't often use footers but this one seemed a good occasion!) to pick up the Field 1, 2 and 3 entries. Once more using the 'Preceeeding String' feature - so I hope your real report offers the same friendly prompts!

                       

                      I also noticed that you mentioned that the generic records do not have a Userfield2 but do have a Userfield3. The sample layout still appears to have a userfield2. If the real one does not have the field it should still be OK - unless userfield3 replaces userfield2 AND has the same 'name', in which case things are a little more complicated.

                       

                      This should give you all the fields you may need. Any field that does not exist in a particular data grouping should now be BLANK on the appropriate row in the table.

                       

                      If you let me know your email address I can send the sample file I generated and the model for you to have a look around for yourself. You should be able to see my email address but, if not, send me a private message with your email details and I will respond.

                       

                      I hope this helps. There may be a few other approaches on this one depending on the format details and consistency (or otherwise) of the 'real' report. Equally I may have assumed something that cannot be assumed, so we may need to look at it afresh if this does not get you to where you want to be!

                       

                      Grant P

                       

                       

                       

                       

                       

                       

                       

                      Originally posted by GrantB:

                      Tom & Grant,

                      Thanks for your help and suggestions.  Tom, I think you're on the right track as far as understanding my issue.  I'm a new Monarch user so maybe the calculated field with some logic is the correct solution.  I've posted a sample of my problem data below.  The data didn't paste with the same format it actually has but I don't think that will affect the solution.  Tom, I'll send you an e-mail directly with the data in a better format.  Grant, if it helps I can send an e-mail directly to you as well.

                       

                      Some brief explanation - each distinct section of data is separated by the line that has Userfield1 and Userfield2.  You can see the first level of data lists a head office and associated delivery terms.  The second level of data has no head office and no delivery terms.  This is because these prices are not specifically associated with any head office - they are intended to stand independently.  Currently, my table is automatically filling in the head office and delivery terms information in the second level of data with the information from the first record even though these fields do not apply to this level.  I want the table to leave these fields blank on my second line in the table.

                       

                      XX/XX/2003                         REPORT                                     2

                      -


                       

                      Selling company              28

                      Head office                  36083          CUSTOMER NAME

                      Delivery terms               1211                EXW

                      Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                       

                                                               Valid from 11/11/2002 to 12/31/2003

                          999.99  USD   PER    100 KG FROM      750.000  KG    TT BA

                       

                      Userfield1 : NAME  Userfield2 : NAME

                       

                      Selling company              28

                      Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                      Plant                             E1

                       

                                                               Valid from 11/11/2002 to 12/31/2020

                          999.99  USD   PER    100 KG FROM            1.000  KG    TT BA

                          999.99  USD   PER    100 KG FROM   11,250.000  KG    TT BA

                          999.99  USD   PER    100 KG FROM   22,500.000  KG    TT BA

                       

                      Userfield1 : NAME  Userfield2 : NAME      Userfield3 : LIST

                       

                      Selling company              28

                      Head office                  586203         CUSTOMER NAME

                      Delivery terms               1211                EXW

                      Material number              00183613  PRODUCT CT750 KG        00010838 00059436

                       

                                                               Valid from 11/11/2002 to 12/31/2003

                          999.99  USD   PER    100 KG FROM      750.000  KG    TT BA

                       

                      Userfield1 : NAME  Userfield2 : NAME /b[/quote]

                      • Inconsistent sort levels
                        GrantB _

                        The preceding string function was the key to the solution of this issue!  As soon as I used that function to define my fields, the table pulled exactly the data I needed.  Thank you so much for your help in solving my problem!

                         

                        Grant