9 Replies Latest reply: May 15, 2014 10:07 AM by Amarjit _ RSS

    Data Extaraction from Header Template

    Amarjit _

      Hi,

       

      Few days back, I got help from this forum, to capture multiple line fields, which I was able to do after creating header template. Then I used some calculated fields to extract data from header template, which I thought, was succefully done, but I was wrong, and I see some problem from the data extarction.

       

      So, here is my problem                                                                               

                

      Sort by Subinventory, Item                           Inventory Value Report                                 Page:  223                                          

               Cost Type:  Frozen                              Detailed       ( USD)                                                                               

      Category Set:  Cost                                                                               

      Subinventory: Location 1                                                      Asset: Yes                                                                               

      Planning MRP        Item                                                                               

      Item            Description    Rev Category        Method   M/B  Asset Status     UOM     Quantity      Unit Cost   Extended Value                              

      -


      -


      --- -


      -


      -


      -


      -


      --- -


      -


      -


                                      

      xxx-                            A   COSTED                         Yes              EA                                                         

      xxxxxxxxxxxxxxx                                                                               

      -x                                                                               

      -


                                                                                       

      Subinventory Location 1 Total:                          000.0000000                                                                               

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

      Cost Element          Account                                            Balance                                                                               

      -


        -


        -


                                                                                       

      Material              000-000-000-0000-000                           000,000.00                                                                               

      Material Overhead     000-000-000-0000-000                                 0.00                                                                               

      -


                                                                                       

      Total:                                                               000,000.00                                                                               

      Subinventory: Location 2                                                  Asset: Yes                                                                               

      Planning MRP        Item                                                                               

      Item            Description    Rev Category        Method   M/B  Asset Status     UOM     Quantity      Unit Cost   Extended Value                              

      -


      -


      --- -


      -


      -


      -


      -


      --- -


      -


      -


                                      

      xxx-xxxxxxx                    A   COSTED                          Yes     EA                                                      /CODE                                                                               

      As you can see in the code, I defined the upper section from

       

      "Sort by Subinventory, Item                           Inventory Value Report                                 Page:  223                                          

               Cost Type:  Frozen                              Detailed       ( USD)                                                                               

      Category Set:  Cost                                                                               

      Subinventory: Location 1                                                      Asset: Yes                                                                               

      Planning MRP        Item                                                                               

      Item            Description    Rev Category        Method   M/B  Asset Status     UOM     Quantity      Unit Cost   Extended Value                              

      -


      -


      --- -


      -


      -


      -


      -


      --- -


      -


      -


                       "[/CODE]

       

      as Header template, to capture the multiple lines. Now, I want to capture the "Subinventory" from the section defined in the header template as well as from non header template.

      As shown in the above example: "subinventory: Location 1" lies in the header template and "subinventory: Location 2" is outside the header template.

       

      Can you please suggest a way to capture this data, which lies inside as well as outside header template.

       

      Please let me know, if the question is not clear.

       

      Your help will be greatly appreciated.

       

      Thanks,

      Amarjit

        • Data Extaraction from Header Template
          Olly Bond

          Hello Amarjit,

           

          If you don't bother capturing the Subinv field in the page header, but instead define a new single line template of type "Append" with "Subinventory" as the trap, you should be OK.

           

          HTH

           

          Olly

          • Data Extaraction from Header Template
            Grant Perkins

            Hi Amarjit,

             

            Ok, looking at your report sample I can now see the problem that you mentioned in the original post.

             

            Is it coorect that in the case of Subinventory level 1 that value exists ONLY in the Page header?  I.E. it has not previously beed 'reported' on the page before in the same way that Subinventory Level 2 is reported in the middle of the page.

             

            'Normally' the Subinventory data would just require an Append template but it there are times when the Append level, for some reason known only to the person who created the report, is included in Page Header area you doi indeed have a challenge to solve.

             

            I have dealt with similar situations before (but not recently!). As I recall the answer was to trap both fields and then compare them, with full knowledge of the report and another other aspects of it that might be helpful or, perhaps, problematic, in order to define some rules by which you can decide which is the final value you need to select.

             

            Sometime it is easy - for example of the Subinventory data values have been sorted into order in the report sections you probably only need to check something simple like which of the two fields has the higher value. Be careful if there is an even higher level of sort within the report which wouold lead to the Sublevel values being repeated within higher level sections.

             

            Does every new Page header have a value or are some of them blank?

             

            When you are developing a solution here it may be useful to include a field for Page() to that each change of page is quite visible in the table and cab, if needed, be used to assist with the selection rules.

             

            This is the point at which I would be assessing the report very carefully to see if there are any especially awkward sections that defy attempts at rule making.

             

            Let us know if these ideas help you and what, if anything, turns up in that analysis to create new challenges.

             

             

            HTH.

             

             

             

            Grant

              • Data Extaraction from Header Template
                Amarjit _

                Hi Grant,

                 

                Thanks for the reply. Please see the explanation below.

                 

                Is it coorect that in the case of Subinventory level 1 that value exists ONLY in the Page header? I.E. it has not previously beed 'reported' on the page before in the same way that Subinventory Level 2 is reported in the middle of the page[/QUOTE]

                 

                No, that's not correct. The "Subinventory level 1" may exist outside the page header.

                 

                As to your suggestion, I created the rules, and it does work for most of the cases, but not all. I extracted the data from Header template as well as the append template, and I made the rule so that the data from append template, takes precedence over from Header. But I found out, this is not always true.

                 

                Does every new Page header have a value or are some of them blank?[/QUOTE]

                 

                Yes, Every page header has a value.

                 

                Now, while I was looking very carefully at the report, I can use a value "Account" to make the report. If the account number changes, the subinventory must change, the reverse may not be true. So, If I try to make a new column which comapre the Account field and Subinventory field, and it gives me the value, whenever Account no changes but the inventory remains same, other wise it remains blank, then I can use that column to make new rules.

                 

                Thus, can you help me to create a column which is populated, only when the "Account" column changes AND the Subinventory coulmn remains same.

                 

                I can use that column to make further rules then.

                 

                Thanks,

                Amarjit

                  • Data Extaraction from Header Template
                    Amarjit _

                    Hi Grant/Olly,

                     

                    I have an other approach, which might work, but in order to get it working, I have a fundamental question:

                     

                    Do we have an option to stop populating a particular field after certain number of occurences?

                     

                    i.e, I can create an Append template to read the subinventory from the non header area, and then I want to stop populating the values after some specific number of same occurances (e.g after 56 times). Let's say, I have subinventory as "Location 1", which I trap through the append template, but I want it to be populated not more than 56 times, after that I want the field to be blank, until the Append template finds a different subinventory.

                     

                    Please let me know, if you know a way to make a calculated field, or lookup field to do?..or any other way......

                     

                    and let me know, if the aproach is not clear.

                     

                    Thanks a lot!!!

                     

                    Amarjit

                      • Data Extaraction from Header Template
                        Grant Perkins

                        Hi Grant/Olly,

                         

                        I have an other approach, which might work, but in order to get it working, I have a fundamental question:

                         

                        Do we have an option to stop populating a particular field after certain number of occurences?

                         

                        i.e, I can create an Append template to read the subinventory from the non header area, and then I want to stop populating the values after some specific number of same occurances (e.g after 56 times). Let's say, I have subinventory as "Location 1", which I trap through the append template, but I want it to be populated not more than 56 times, after that I want the field to be blank, until the Append template finds a different subinventory.

                         

                        Please let me know, if you know a way to make a calculated field, or lookup field to do?..or any other way......

                         

                        and let me know, if the aproach is not clear.

                         

                        Thanks a lot!!!

                         

                        Amarjit[/quote]

                         

                        Amarjit,

                         

                        I can understand what you are asking but I'm not sure how you would make use of it.

                         

                        The basic answer to the question is "no".

                         

                        I suppose it may be possible to create some way of forcing such a result usning calcualted field(s) as substitues for the the original field(s) but is seems a bit extreme.

                         

                        Version 10 has some new fnctionality that provides some further control over where templates start and stop but that does not include anything by number of lines or number of records.

                         

                        It sounds like you may be heading towards some complicated ideas for a solution and, in my experience, that is a good point to stand back, take a break, and seek something much simpler, even if that means looking at the possibility of a 2 stage process. Each stage can be made simpler than anything you would be trying using calculated fields.

                         

                        Do you have a realistic version of the report that you could share with us so that we can see exactly what it does and so makes it easier to suggest the best way forward. It sounds like we might need at least several pages that include examples of the problem areas but if you have a whole report that too would be fine by me.

                         

                         

                        HTH.

                         

                         

                        Grant

                          • Data Extaraction from Header Template
                            Amarjit _

                            Hi Grant,

                             

                            Thanks for the reply. Yes, I think a 2 stage approach is best for my case. Actually, right now, I have created 2 model files, and I use them to get my final output. I am not sure how to automate the process.

                             

                            By the way, I have a different problem to deal with now, We just upgraded to office 2007, and now when I copy the Motnarch Table to excel, all the couluns is copied in a single column of excel. Do you have any idea to fix this??

                             

                            Thanks,

                            Amarjit

                              • Data Extaraction from Header Template
                                Data Kruncher

                                The best way to handle running multiple step Monarch process such as what you've developed is with a batch file.

                                 

                                You can download and use the free [URL="http://********************/tips/monarch-batch-file-generator"]Monarch Batch File Generator[/URL] to help you build the proper command lines for each process, and then combine the Monarch.exe lines into a new batch file. Just be sure to prefix the first Monarch line with the DOS command named START, then an identifier, and a /WAIT directive, then the rest of the command.

                                 

                                In the end your new batch file should look something like this:

                                START "Pass 1" /WAIT "C:\Program Files\Monarch\Program\Monarch.exe /prj:"C:\MyFolder\MyProject1.xprj" /pxall

                                "C:\Program Files\Monarch\Program\Monarch.exe  /prj:"C:\MyFolder\MyProject2.xprj" /pxall

                                /codeAs for the upgrade to Excel 2007, you won't be able to copy/paste from Monarch v7 to Excel 2007 and maintain separate columns as you expect, like you used to have with the earlier Excel version.

                                 

                                The only way that you can get distinct columns is to export to an Excel file using Monarch's export wizard, and then open the file in Excel as you would normally. This is yet another big reason to upgrade your Monarch software. The time that you spend doing this one basic task instead of simple copy/paste will make the upgrade pay for itself in no time.

                                  • Data Extaraction from Header Template
                                    Amarjit _

                                    Hi Data Kruncher,

                                     

                                    Thanks for referring to the Monarch Batch File Generator. I will go through in detail and learn to create the batch file.

                                     

                                    I used the Export wizard, and it works. As per the upgrade of Monarch, I can only suggest to our Management team.

                                     

                                    Thanks again for the help.

                                     

                                    Amarjit

                            • Data Extaraction from Header Template
                              Grant Perkins

                              Hi Grant,

                               

                              Thanks for the reply. Please see the explanation below.

                               

                               

                               

                              No, that's not correct. The "Subinventory level 1" may exist outside the page header.

                               

                              As to your suggestion, I created the rules, and it does work for most of the cases, but not all. I extracted the data from Header template as well as the append template, and I made the rule so that the data from append template, takes precedence over from Header. But I found out, this is not always true.

                               

                               

                               

                              Yes, Every page header has a value.

                               

                              Now, while I was looking very carefully at the report, I can use a value "Account" to make the report. If the account number changes, the subinventory must change, the reverse may not be true. So, If I try to make a new column which comapre the Account field and Subinventory field, and it gives me the value, whenever Account no changes but the inventory remains same, other wise it remains blank, then I can use that column to make new rules.

                               

                              Thus, can you help me to create a column which is populated, only when the "Account" column changes AND the Subinventory coulmn remains same.

                               

                              I can use that column to make further rules then.

                               

                              Thanks,

                              Amarjit[/quote]

                               

                              OK. So if the Subinventory Level data is always reported in the main body of the report you can use Olly's suggestion of the Append template and ignore the value in the Page header as it seems to be there just as a repeat of information from the previous page. The Append template values are not, so far as I am aware, limited to work on a single page.

                               

                              For your question about checking for multiple field changes I thinkn the answer is No, or at least that you cannot make that sort of check (comparing data from the previous row with the current row) in the table as part of the extraction. If you can populate 2 fields within a record you can compare them but not adjacent fields in different rows of the same column.

                               

                              However if (or when) you have such a need that cannot be addressed any other way the normal approach would be a 2 stage process.

                               

                              In the first stage you would produce a table (or maybe a summary from the table) that will give you an export file (i.e. a new report to use as an input file) that has a structure suitable for your final analysis. You then export that file, read it back into Monarch in a new session and apply a new model to it to get at the information you need

                               

                              You could use this idea for you Account and Inventory number changes question. (I think if I have the correct impression of what you are seeing.)

                               

                              When you export to create the new input file you can specify that you do not want to show consecutive duplicate values in a field. Therefore you would only have an entry in that field of the value changes. If I understand correctly that would give you the comparison checks you need.

                               

                              You can run a 2 stage (or indeed a multiple stage) Monarch process using a single batch file if you need to automate the work and make it simple to run.

                               

                              HTH.

                               

                               

                              Grant