38 Replies Latest reply: May 15, 2014 10:06 AM by Grant Perkins RSS

    Urgent! newbie needs help

    joogi _

      Hi all,

       

      I am new to the forum and I have a dump question. My company is using v4 and I only used it for a couple of times a few years ago. Last week, I came across an report that needs to be extracted. So I pulled out the Monarch installed on my computer. Long story short.... I did the traps (details, append), but nothing was greyed out. I believe the content of the report should be greyed out once the traps are done. When I flip to table view, nothing is extracted. Where did I do wrong?

       

      Thanks.

        • Urgent! newbie needs help
          joogi _

          hmm.... can someone help me? This is probably a simple question for you guys. My problem is I don't own the user manual, so I cannot look it up.

           

          Would it help if I do a screenshot to show what I did to the traps, etc?

            • Urgent! newbie needs help
              Data Kruncher

              Yes, screenshots help, but keep in mind that you can't upload anything directly here, so you have to use a service site like Photobucket or something.

               

              Also, you can post sample reports using CODE tags. See the sticky post at the top of the forum. This is likely more valuable for troubleshooting than screenshots as we can copy and paste the sample to work on it on our end.

               

              I'm going to be in and out for the rest of the day so I may or may not be able to assist. I only say that because I don't want you to think that I'm ignoring you if I don't reply quickly today. Of course everyone else can (and likely will) chime in if they can.

                • Urgent! newbie needs help
                  joogi _

                  I have 1000+ pages of this report in each month and currently saved as .txt format. I did try to trap the characters at the lowest detail. The problem is, nothing was trapped by Monarch when I clicked the preview button. It seems like I am totally messed up, even the very basics... 

                   

                                                                                                  FROM: 01/01/2009  TO: 01/31/2009

                   

                  BU  RESP  Account Number      Description                G/L Transaction        Loc.     Debit          Credit          Net

                  --  -


                    -


                    -


                    -


                    -


                    -


                    -


                     -


                  12  2000  73200   1032  3301  HP LD INT                  HP CC INTL                             .00          37.86          -37.86

                                                                            HP LD INT                              .00       68934.61      -68,934.61

                                                                                                  -


                    -


                     -


                                                                                                  .00       68972.47      -68,972.47

                   

                             73200   1033  3301  HP NA LD                   HP 10 NA                               .00         362.67         -362.67

                                                                            HP 5 NA                                .00        1524.33       -1,524.33

                                                                            HP 7 NA                                .00         636.54         -636.54

                   

                  /CODE

                    • Urgent! newbie needs help
                      Joe Berry

                      joogi,

                       

                      I do not have access to system that will install v4; however, I just pulled it into v10.5 and trapped using the alpha characters in the G/L Transaction and was able to trap all fields with no issues.This route traps all non-total lines.

                       

                      Sorry I couldn't be more help, but v4 is circa 1998, and I can get it to install even when I run it in compatibility mode.

                       

                      Joe

                      Peoria

                        • Urgent! newbie needs help
                          joogi _

                          First of all... is it correct to open up the .txt file in Monarch; then highlight a sample line then create New Template? Then start the trapping?

                           

                          I just tried what you did (re: trapped the G/L), nothing happend - no greying in the report.

                            • Urgent! newbie needs help
                              Joe Berry

                              You have the basics correct.  Can you re-install the program? 

                               

                              Other than that I have no additional advice.  Anyway you can talk your employer into upgrading?

                                • Urgent! newbie needs help
                                  Data Kruncher

                                  You'll have a tough time getting support for v4 here it seems. Screenshots might help in that case.

                                    • Urgent! newbie needs help
                                      joogi _

                                      Here it is:

                                      http://i144.photobucket.com/albums/r162/joogi/monarchv4screenshot.png[/url]

                                       

                                      The program worked 2 years ago... and I won't be able to get it re-installed as I don't own the CD and it's not a typical program that is used company-wide. So IT can't do anything.

                                       

                                      Hope you guys can help me here....

                                       

                                      Actually just realized it's v5, not v4... doesn't help much

                                        • Urgent! newbie needs help
                                          Data Kruncher

                                          Use NNNNN to trap the account number instead of AAAAAAA to trap the letters for the G/L Transaction text.

                                           

                                          Alpha traps (A) should only be used when you want a letter (A to Z) in that particular column. By defining a whole collection of them together, you're telling Monarch that you want to trap rows which only have letters in every[/B] position in which you've put an A trap.

                                           

                                          That will get you started, and depending on the rest of the report might be all you'll need.

                                           

                                          Kruncher

                                            • Urgent! newbie needs help
                                              joogi _

                                              thing seems starting to work, at least some lines are trapped!

                                               

                                              but I have more problems. Following what you suggested, it doesn't trap lines with dollar amounts and GL Transaction but without account numbers. It seems that this report is formatted in such a way that if the account/BU/Resp/Description are the same, they won't be repeated. Almost like a subset. It looks like GL Transaction and dollar amounts (not the sub-totals) are the lowest details.

                                               

                                              Joe Berry was able to trap GL Transaction using alpha characters..

                                               

                                              EDIT: And what if I need to trap the date 01/31/2009 and the provinces above it (not showing on the screenshot)? This report comprises of details for 12 provinces and I have 12 monthly reports.....

                                               

                                              still need help!

                                                • Urgent! newbie needs help
                                                  Grant Perkins

                                                  EDIT: And what if I need to trap the date 01/31/2009 and the provinces above it (not showing on the screenshot)? This report comprises of details for 12 provinces and I have 12 monthly reports.....

                                                   

                                                  still need help![/quote]

                                                   

                                                  Use an Append template - probably a Page Header append type would suit well there but I can't be sure without seeing more of the report - to trap the date information. My guess is you can grab the province information in the same append but if that is not quite consistent enough throughout the report just create an Append separately for the Province information.

                                                   

                                                  With luck the dates will be consisently positioned across the line so trapping on FROM: and/or TO: is likely to be all you need. However if you can grab the Province info at the same time a multi-line sample and trap on whichever line in that sample proves most consistent should do the trick.

                                                   

                                                  HTH.

                                                   

                                                   

                                                  Grant

                                                    • Urgent! newbie needs help
                                                      joogi _

                                                      Hi Grant,

                                                      It's 5:40am here!!!

                                                       

                                                      I just played with the report a bit again, and thanks for your replies.

                                                      You are correct with the GL Transaction, there are actually numeric for the 1st character, so I used non-blank to trap. And you are correct as well with regards to trapping the amounts.. that's what I did..

                                                       

                                                      Is 'Fill from previous line' option in the field definition available in v5?

                                                       

                                                      Now that I skimmed through the report in more details, I found out another problem... There are lines without GL Transaction and Account, but it has Description and dollar amounts...... will append template help?

                                                        • Urgent! newbie needs help
                                                          joogi _

                                                          I think this page is the most representational as to how the data display on the report....

                                                          I just did a couple of append templates. When I flip over to Table view, lots of columns or rows are repeating. Did I do something wrong with the auto-define fields? How many append templates do I need here with all the different combinations?

                                                           

                                                          First 5 numbers of the Account Number are most important, so is BU and RESP even though it's not showing in the sample here. It seems very difficult for a newbie!

                                                           

                                                           

                                                                                                                     Business entity : Canada                                                                               

                                                          Sub-Market:       Alberta                                                                               

                                                          FROM: 01/01/2009  TO: 01/31/2009                                                                               

                                                          BU  RESP  Account Number      Description                G/L Transaction        Loc.     Debit          Credit          Net        

                                                          --  -


                                                            -


                                                            -


                                                            -


                                                            -


                                                            -


                                                            -


                                                             -


                                                             

                                                          34  4000                      GST/HST PAYABLE                                                  1.00            .00            1.00 

                                                                                         AAAA - CDA AAAAAAA                                                .00           4.00           -4.00                                                                               

                                                          -


                                                            -


                                                             -


                                                                                                                                           

                                                          1.00           4.00           -3.00                                                                               

                                                          04317               PST RECEIVABLE - ONT       PST RECEIVABLE - ONT                 41.74            .00           41.74                                                                               

                                                          -


                                                            -


                                                             -


                                                                                                                                           

                                                          41.74            .00           41.74                                                                               

                                                          14310               FIXED ASSETS               FIXED ASSETS           DIGI            .00          12.42          -12.42                                                                               

                                                          H. O            .00         315.35         -315.35                                                                               

                                                          TNS             .00          17.53          -17.53                                                                               

                                                          TRAN            .00          18.27          -18.27                                                                               

                                                          -


                                                            -


                                                             -


                                                                                                                                           

                                                          .00         363.57         -363.57                                                                               

                                                          23001   1500        AAAAA AA ACCTS. REC.      AAAAAAA AA ACCTS. REC.          81360155.31    85109316.38   -3,749,161.07                                                                               

                                                          H. O            .00         335.89         -335.89                                                                               

                                                          -


                                                            -


                                                             -


                                                                                                                                           

                                                          81360155.31    85109652.27   -3,749,496.96                                                                               

                                                          23200   0089  3301  ABC GOODWILL CR            ABC GOODWILL CR                    3756.46         180.66        3,575.80 

                                                                                                                    VALUED CUST ABC CR                   99.75            .00           99.75                                                                               

                                                          -


                                                            -


                                                             -


                                                                                                                                           

                                                          3856.21         180.66        3,675.55 

                                                           

                                                          /CODE

                                                            • Urgent! newbie needs help
                                                              Olly Bond

                                                              Hello joogi,

                                                               

                                                              If you change your trap characters to use one single non-blank trap in the left hand column of the dsescription field, and a decimal period in the relevant column of the Net amount, that should pick out what you want from the selection you posted.

                                                               

                                                              You may or may not need an append template to handle the BU and RESP fields - if your version supports the option to copy blank values from previous entries on the advanced tab of the field properties dialog, then you can pick these up on the detail.

                                                               

                                                              HTH,

                                                               

                                                              Olly

                                                              • Urgent! newbie needs help
                                                                Grant Perkins

                                                                I think this page is the most representational as to how the data display on the report....

                                                                 

                                                                 

                                                                It seems very difficult for a newbie!

                                                                 

                                                                /quote

                                                                 

                                                                Well, it's a matter of familiarisation to start with then it gets easier. However your report is certainly unhelpful in places and it's a little difficult to advise you specifically without some immersion in the report and its vagaries.

                                                                 

                                                                You should have a 'fill from previous line (or is it record?) available in V5 but the terminology may vary. I don't have a running V5 system to double check.

                                                                 

                                                                I am assuming you need all of the lines with entries on the value columns EXCEPT the subtotal lines. Is that correct?

                                                                 

                                                                If so, as I feared earlier, the report is unhelpful in some ways. Whilst all lines have the decimal point trap potential not all of them have any additional consistent trap options in other columns. That means it is not obvious how one would exclude the subtotal lines using the trapping options.

                                                                 

                                                                The way around that would be to trap using the decimal point character and then filter out the unwanted subtotal lines. To do that you may need to resort to checking whether several other fields are blank - in which case 'filling' the fields, by 'from previous record' or by Append template may not be such a good idea.

                                                                 

                                                                There will be other options for the approach but this innocuous looking little report seems to be more of a challenge than one might think at first sight.

                                                                 

                                                                So, what do you need to get out? We can probably get everything in one pass (more than one pass is acceptable here.) but it you can be specific about the required extraction it is possible that a simpler solution may be available.

                                                                 

                                                                A simple record of the extracted result with the required fields indicated should do fine.

                                                                 

                                                                 

                                                                Grant

                                                                  • Urgent! newbie needs help
                                                                    joogi _

                                                                    This is what I want. Pretty much every field is important. If they (BU, RESP, Acct) are blank in the report, it's assumed that they are the same as above as the report is structured. Lines with Description without GL Transaction need to be picked up as well. Ultimately if I do a grand total to the amount columns, it should tie to the grand total (total market) on last page of the report. This is crucial for auditing purpose.

                                                                     

                                                                    Then I will filter or pivot whichever way I want in excel to pull out specific account number or GL transaction or description. I do not want to filter it in Monarch (if this function is available in later version), as again, I need the total for auditing purpose in order to prove that what I extracted is complete.

                                                                     

                                                                     

                                                                     

                                                                    Period          Province     BU     RESP     Account Number     Description          G/L Transaction          Debit     Credit     Net

                                                                    01/01/2009     Alberta          34     4000               PST RECEIVABLE - ONT     PST RECEIVABLE - ONT     41.74     0.00     41.74

                                                                    01/01/2009     Alberta          34     4000               GST/HST PAYABLE                         1.00     0.00     1.00

                                                                    01/01/2009     BC          34     4510     4319                         NNNNNNN               100.00     0.00     100.00

                                                                    /CODE

                                                                      • Urgent! newbie needs help
                                                                        Grant Perkins

                                                                        Then I will filter or pivot whichever way I want in excel to pull out specific account number or GL transaction or description. I do not want to filter it in Monarch (if this function is available in later version), as again, I need the total for auditing purpose in order to prove that what I extracted is complete.

                                                                         

                                                                         

                                                                        /quote

                                                                         

                                                                        The point about the filtering is that the subtotals will be in Monarch but de-selectable (or selectable even!) depending on how you filter. I'm assuming you would not want to simply add them back into other summations and so double (or something) the values. Whatever you want to do with them you need a way to identify that the subtotal values are from the subtotals rather than the individual transaction lines so that you can use them as you need to and to be able to establich which higher level codes should be associated with them and which should not.

                                                                         

                                                                        It may be worth extracting the subtotal lines using a Footer Template - especially if you make use of Monarch's powerful Summaries prior to sending the data to Excel.

                                                                         

                                                                        More shortly.

                                                                         

                                                                         

                                                                        Grant

                                                                          • Urgent! newbie needs help
                                                                            Grant Perkins

                                                                            joogi,

                                                                             

                                                                            I'm beginning to think you have one of those reports that looks innocent but reveals itself to be the devil incarnate as one gets stuck into it.

                                                                             

                                                                            It would likely be easier to grapple with the most recent version of Monarch. As it is it's quite a challenge, or so it seems. It's separating the subtotals from the rest that is the problem. Each basic need is easy enough - but the whole is not.

                                                                             

                                                                            Do you have V5 standard or V5 Pro?

                                                                             

                                                                            Grant

                                                                              • Urgent! newbie needs help
                                                                                joogi _

                                                                                It says v 5.02. So I guess it's Standard. There's no way my company will get a recent version because this version was originally owned by a girl in our dept (don't know why she needed it from the beginning). I got the software installed on my computer a couple of years because just one time my team required to extract report data at year-end. We rarely require data extraction like this; just once in a blue moon.

                                                                                 

                                                                                hmm.. I don't understand what you mean the subtotals are the problems. I thought this kind of reports with subtotals are quite common?

                                                                                 

                                                                                joogi,

                                                                                 

                                                                                I'm beginning to think you have one of those reports that looks innocent but reveals itself to be the devil incarnate as one gets stuck into it.

                                                                                 

                                                                                It would likely be easier to grapple with the most recent version of Monarch. As it is it's quite a challenge, or so it seems. It's separating the subtotals from the rest that is the problem. Each basic need is easy enough - but the whole is not.

                                                                                 

                                                                                Do you have V5 standard or V5 Pro?

                                                                                 

                                                                                Grant[/QUOTE]

                                                                                  • Urgent! newbie needs help
                                                                                    Grant Perkins

                                                                                    It says v 5.02. So I guess it's Standard. There's no way my company will get a recent version because this version was originally owned by a girl in our dept (don't know why she needed it from the beginning). I got the software installed on my computer a couple of years because just one time my team required to extract report data at year-end. We rarely require data extraction like this; just once in a blue moon.

                                                                                     

                                                                                    hmm.. I don't understand what you mean the subtotals are the problems. I thought this kind of reports with subtotals are quite common?[/quote]

                                                                                     

                                                                                    I can't recall what the 'About' contained in V5. If you don't see an 'Open database' near the 'Open Report' option in the file menu then you don't have the Pro version. V5 was the first version to offer the direct database read so there is a good chance that it is indeed 'Standard'.

                                                                                     

                                                                                    The challenge of the subtotals in this example is that I can't so far see a reliable way to identify those lines once the other fields have been 'filled' using the 'Copy values from previous' option. There are options for doing comparative field value checks and setting a suitable flag in a calculated field to say 'This is a subtotal record' - or there would be except that the comparative value idea fails where the real detail is only one line long and therefore matches the sub-total. The second data 'block' in your sample is an example of the problem. If the Subtotal line had a text string associated with  it - 'Totals' for example - life would be simple ...

                                                                                     

                                                                                    If by chance you had the Pro version we could get it to work by using a 2 step process (I think) but that may not be so easy to construct with Standard Monarch.

                                                                                     

                                                                                    You mention a lot of pages  - how many actual data records do you estimate will be created for the Excel input? Can you estimate how many of those will be subtotals? I'm looking for inspiration for an alternative approach ...

                                                                                     

                                                                                     

                                                                                     

                                                                                    Grant

                                                                                      • Urgent! newbie needs help
                                                                                        Grant Perkins

                                                                                        Ok, here we go. This may cover too much ground and may not be the neatest approach but there are several options and questions and I suspect some part of this will get you to (or near to) where you want to be.

                                                                                         

                                                                                        DETAIL TEMPLATE.

                                                                                         

                                                                                        Trap the lines with transaction values using the decimal points.

                                                                                         

                                                                                        Define the fields you want to capture. I figure it will be all the fields except "Loc", working right to left, possiboly up to the Account field. Not sure if that would be best in the detail or in its own append. Why? Well the detail records would seem to require the 'Copy from previous record' option to be set for the Account but I'm not sure about the same thing for the Description and Transaction fields, based on your 'What I need to get out' example. So you could potentially deal with the Account field either way.

                                                                                         

                                                                                        This template will extract all the numeric value lines, including the subtotals. The subtotals will only be valid if the order of the report data is not changed (or the data filtered) in Excel (or Monarch!).

                                                                                         

                                                                                        APPEND TEMPLATE 1

                                                                                         

                                                                                        Now create an Append template using the exact same decimal point trap and paint a single long field that stretches from the Account field all the way to Loc. Call it something like 'Subtotal Line checker'. For Subtotal lines this will be an empty field and so gives a way to ID the subtotal lines when all other lines should have at least one of the fields filled, even if only the LOC field. (At least so far as the sample data shows ....)

                                                                                         

                                                                                         

                                                                                        APPEND TEMPLATE 2

                                                                                         

                                                                                        If you run with the Account fields as an Append extraction create that append template now, trapping on a character at the beginning of the account number column.

                                                                                         

                                                                                        APPEND TEMPLATE 3

                                                                                         

                                                                                        Add another append template to capture the BU and RESP columns trapping as required over on the left of the report.

                                                                                         

                                                                                         

                                                                                        PAGE HEADER TEMPLATE

                                                                                         

                                                                                        Add another append as a PAGE HEADER type for the lines that include Business Entity through to the dashed lines under the column headers. Paint the fields you need from that block - I assume Business Entity, Sub Market and the 'To:' date.

                                                                                         

                                                                                        Now if you look at the table you should have all the fields you need with certain columns filled (or unfilled) if empty according to the field definition.

                                                                                         

                                                                                        Let's tidy the Subtotal ID facility a little. Go to the Data menu in the tabkle window and look for the option to add a calculated field to the table. (I don't recall exactly how it was termed in V5). Create the basis of the field as a numeric field and in the formula use the ISBLANK() function (I think this was available in V5) to check for the large (empty) field that will Identify the subtotal line. ISBLANK returns a 1 or 0 and you can use that to filter to include or exclude the subtotal lines as required. The large 'empty' field can be hidden. No need to take that to Excel.

                                                                                         

                                                                                        Now at that point, and after creating a few filters and sorts, I would be off into the Monarch summaries to start my detailed analysis but I get the impression you will be doing that in Excel - in which case here is where you will either cut and paste to Excel or run the Export process (was it called Export in V5? Memory does not tell me for certain.)

                                                                                         

                                                                                        I would send you the model I have generated whilst experimenting but it is not backwards comptible to V5 - so we will just have to do ths the harder way. See where you get to with that and I will pick up on anything that is not clear tomorrow if you let me know how you get on.

                                                                                         

                                                                                        HTH.

                                                                                         

                                                                                         

                                                                                        Grant

                                                                                          • Urgent! newbie needs help
                                                                                            joogi _

                                                                                            Thanks Grant. I will give it a try and let you know... just remember I only used Monarch v5 for a couple of times and it was a crash course back then!

                                                                                             

                                                                                            This will probably take me a few days to figure out by following the steps you illustrated here... :o

                                                                                             

                                                                                            EDIT: guess what.. there's no decimal point trap in v5.. so I use NN to trap the decimal places in Debit Credit and Net columne to avoid trapping the date 01/31/2009 which happens to be in the same position as the Net amount but at the very top of every page in this report (from the far right)....

                                                                                              • Urgent! newbie needs help
                                                                                                Grant Perkins

                                                                                                Thanks Grant. I will give it a try and let you know... just remember I only used Monarch v5 for a couple of times and it was a crash course back then!

                                                                                                 

                                                                                                This will probably take me a few days to figure out by following the steps you illustrated here... :o[/quote]

                                                                                                 

                                                                                                Just think of it as splitting the task into smaller chunks and using the, now relatively simple, detail template as the basis for most of the rest of the activity.

                                                                                                 

                                                                                                If you had something working before, as you mentioned above, you are not far off. Resist thinking of this as a complex thing - it's just a group of simple things so don't be overawed. (I do remember the early days of working with Monarch and how one day things sort of slipped into place in terms of understanding why things worked! So I sympathise with your predicament.)

                                                                                                 

                                                                                                Checkeing that you are getting what you need all through the report may take longer. Sometimes reports have hidden 'issues'. If you can get the extracts you want we could look at using Monarch for checking the subtotals of detail fields extracted. Or you could do that in Excel I guess.

                                                                                                 

                                                                                                Have fun - most important!

                                                                                                 

                                                                                                 

                                                                                                Grant

                                                                                                 

                                                                                                 

                                                                                                 

                                                                                                 

                                                                                                 

                                                                                                 

                                                                                                (Just as a note for others reading this thead;

                                                                                                 

                                                                                                Joogi mentioned the need to be able to audit the numbers to make sure the next stage of analysis is correct. This is a good point to raise.

                                                                                                 

                                                                                                Monarch V10.5 Pro now offers an option to export to Excel a replica of the original report used for the Monarch work along with any table and summary exports defined in the model. The potental benefits for making the audit process slicker should be significant.)

                                                                                              • Urgent! newbie needs help
                                                                                                joogi _

                                                                                                DETAIL TEMPLATE.

                                                                                                 

                                                                                                Trap the lines with transaction values using the decimal points.

                                                                                                 

                                                                                                Define the fields you want to capture. I figure it will be all the fields except "Loc", working right to left, possiboly up to the Account field. Not sure if that would be best in the detail or in its own append. Why? Well the detail records would seem to require the 'Copy from previous record' option to be set for the Account but I'm not sure about the same thing for the Description and Transaction fields, based on your 'What I need to get out' example. So you could potentially deal with the Account field either way.

                                                                                                 

                                                                                                /QUOTE

                                                                                                hmm.. I still don't get it when you said Copy from previous record... Also, there are more than just the Account field that require copy from previous record?

                                                                                                 

                                                                                                 

                                                                                                APPEND TEMPLATE 1

                                                                                                 

                                                                                                Now create an Append template using the exact same decimal point trap and paint a single long field that stretches from the Account field all the way to Loc. Call it something like 'Subtotal Line checker'. For Subtotal lines this will be an empty field and so gives a way to ID the subtotal lines when all other lines should have at least one of the fields filled, even if only the LOC field. (At least so far as the sample data shows ....)

                                                                                                 

                                                                                                /QUOTE

                                                                                                 

                                                                                                How do I paint a single long field... and label it? In the Trap field? I am really unfamiliar with this :o

                                                                                                 

                                                                                                another question, how come I cannot open a saved model after I save it?

                                                                                                  • Urgent! newbie needs help
                                                                                                    Grant Perkins

                                                                                                    hmm.. I still don't get it when you said Copy from previous record... Also, there are more than just the Account field that require copy from previous record?

                                                                                                     

                                                                                                     

                                                                                                    How do I paint a single long field... and label it? In the Trap field? I am really unfamiliar with this

                                                                                                     

                                                                                                    /quote

                                                                                                     

                                                                                                    When you define a template  - let's say for a Detail template but the principle if the same throughout - you select one (or more) lines that represent the detail record you want to capture (one line in your sample but in other reports a single record might cover more than one line) and then use the meny to create a new template.

                                                                                                     

                                                                                                    This opens a 2 line (for your purposes) window across the screen above the report display. The top line is where you enter the trap information and the line below is where you define the field positions to be extracted. (There is also an 'Auto' field selection option that may or may not work for you to select the fields for you. In many cases, your sample being one of them, the sparse population of the data positions in the report may not be a good basis for the auto field feature to use.)

                                                                                                     

                                                                                                    To create a field in the lower line simply place the cursor in the window and drag right (or left) for as many characters as you need. (i'e' 'Paint' the field.) You can then edit the field (right mouse click on the field should work in V5 iirc) to see and amend the properties of the field including re-naming it and, on the 'General' tab (I'm basing this on V6 as I am not sure where my V5 documentation is at the moment) you should, below the main definition fields, a Couple of lines relating to 'Copy Field Values'. The description are self explanatory (at least in V6 they are) and you simply tick in the normal Windows way whichever option you want for the particular field you are editing.

                                                                                                     

                                                                                                    You mentioned in an earlier post that you thought you had nearly got the template working and I assumed that meant you had the trap doing something and had defined some fields that gave you some of what you wanted. Would it be nmore accurate to say that you could see the trap as it would appear to work on the report but had no fields defined?

                                                                                                     

                                                                                                    This is not too difficult to get to grips with but is slightly more challenging via forum exchanges than by direct contact. That said, given the time difference between our locations and the day being what it is, I'm not sure it's a good idea for me to get into a virtual guidance session at this point.

                                                                                                     

                                                                                                    You may find it useful to search around in the Help file - I think it was quite good at that point (and has improved ever since) - and maybe someone else can pick this up for specific help for the next few hours whilst I am otherwise occupied.

                                                                                                     

                                                                                                    HTH.

                                                                                                     

                                                                                                     

                                                                                                    Grant

                                                                                                      • Urgent! newbie needs help
                                                                                                        joogi _

                                                                                                         

                                                                                                        You mentioned in an earlier post that you thought you had nearly got the template working and I assumed that meant you had the trap doing something and had defined some fields that gave you some of what you wanted. Would it be nmore accurate to say that you could see the trap as it would appear to work on the report but had no fields defined?

                                                                                                         

                                                                                                        /Quote

                                                                                                         

                                                                                                        Yes I did, when I was playing around and already overwrote the model. I think I defined something at that time, but not sure if everything was accurately done. At that time I did a detail template, a couple of append ones, but so many columns or values were duplicated.. so ultimately I overwrote the whole thing and lost it.. I wasn't sure what I was doing :confused:

                                                                                                         

                                                                                                        This is not too difficult to get to grips with but is slightly more challenging via forum exchanges than by direct contact. That said, given the time difference between our locations and the day being what it is, I'm not sure it's a good idea for me to get into a virtual guidance session at this point.

                                                                                                         

                                                                                                        You may find it useful to search around in the Help file - I think it was quite good at that point (and has improved ever since) - and maybe someone else can pick this up for specific help for the next few hours whilst I am otherwise occupied.

                                                                                                         

                                                                                                        /QUOTE

                                                                                                         

                                                                                                        I really appreciate your time! When I am back to work next week, I will have no time do this anymore... so hopefully over the weekend and I can get more out of this.

                                                                                                        • Urgent! newbie needs help
                                                                                                          joogi _

                                                                                                          http://i144.photobucket.com/albums/r162/joogi/monarchv4screenshot3.png[/url]

                                                                                                           

                                                                                                          Please kindly take a look at this screenshot. I have a problem here... Each sub-market (province) has a total at the end; then on the very last page of the report, there's a total for the business entity and the market. So the extracted data has lines with totals for each for the 12 sub-markets and 1 total for business entity and another one for the market.

                                                                                                           

                                                                                                          The good news is, I managed to follow what Grant illustrated in one of his responses above I guess I woke up from my dream... :P But then I don't necessary why I have to do these steps.. guess more playing around will help later.

                                                                                                           

                                                                                                          However, I have another question. It seems that the RESP extracted in the detail template do not always tie to the RESP extracted in the append template. When I look at the extracted data in the table view, I spoted many inconsistencies.

                                                                                                           

                                                                                                          EDIT: Example here. The lines above these have BU 34 and RESP 4300. So for Transfer Balance, RESP extracted become 4300 instead of 4710 which is what I expect based on the report. Should I separate the BU and RESP into 2 append templates?

                                                                                                           

                                                                                                               4710  23000   1115        A/R - UTC CLEARING         A/R - UTC CLEARING                64672.73        8863.63       55,809.10

                                                                                                                                                                                          -


                                                                                                            -


                                                                                                             -


                                                                                                                                                                                          64672.73        8863.63       55,809.10

                                                                                                                                                                                          23002   1500        TRANSFER BALANCE           AR BALANCE                             .00        6965.36       -6,965.36

                                                                                                                                                                    TRANSFER BALANC                     206.20         149.24           56.96

                                                                                                                                                                                          -


                                                                                                            -


                                                                                                             -


                                                                                                                                                                                          206.20        7114.60       -6,908.40

                                                                                                          /code

                                                                                                            • Urgent! newbie needs help
                                                                                                              Grant Perkins

                                                                                                              [URL]http://i144.photobucket.com/albums/r162/joogi/monarchv4screenshot3.png[/URL]

                                                                                                               

                                                                                                              Please kindly take a look at this screenshot. I have a problem here... Each sub-market (province) has a total at the end; then on the very last page of the report, there's a total for the business entity and the market. So the extracted data has lines with totals for each for the 12 sub-markets and 1 total for business entity and another one for the market.[/quote]

                                                                                                               

                                                                                                              So the qestion is "Do you need them and if you do what do you want to do with them?"

                                                                                                               

                                                                                                              Your most likely choices are:

                                                                                                               

                                                                                                              Include them with clear identification of the line's purpose in the report.

                                                                                                              /LIST

                                                                                                              Exclude them - they have no real purpose in the export but are useful for checking the validity of the extraction

                                                                                                              /LIST

                                                                                                              Extract the (subtotals and) totals separately for later automatic comparison in Excel. (I would say 'for later automatic comparison in Monarch' but that could be rather convoluted using Monarch V5 Standard so Excel would probably be easier for you.)

                                                                                                              /LISTTo exclude them you either need to devise a Trap that will work for everything else except the ultimate Total/Sub-total lines on the report OR just accept whatever is extracted and then filter them from the visible data table you work with. Either approach is fine as is a combination of the approaches if required.

                                                                                                               

                                                                                                              If you want to include them then you are already capturing them but the 'naming' information to the left of the values is messy so you may need to revisit how you extract those lines. The alternative would be to have a separate model to extract just those types of lines and anything else you need that tells you what grouping of records they relate to in a form that allows you to link back to totals generated in, say, Excel. In other words you need codes in order to run a lookup not just the descriptions related to the codes as they are shown in the sample.

                                                                                                               

                                                                                                              These totals lines would typically be extracted using a FOOTER type append template BUT with V5 you have only one Footer template to apply. (More recent versions offer more.) Therefore you may have some decisions to make about how you wish to work with the totals lines.

                                                                                                               

                                                                                                              Is your objective simply to translate the report just as it is but in field based columns to be imported into Excel? Or do you plan some other preparation work - re-grouping data, other forms of analysis and so on - before taking it to Excel?

                                                                                                               

                                                                                                              Are you expecting to automate the totals/subtotals checking processing after the data hits Excel or will that be a 'manual' process by, for example, checking Excel subtotals against the values on the report?

                                                                                                               

                                                                                                              How you want to approach this will very likely influence the best approach to take in the Monarch modelling work.

                                                                                                               

                                                                                                               

                                                                                                               

                                                                                                              The good news is, I managed to follow what Grant illustrated in one of his responses above I guess I woke up from my dream... :P But then I don't necessary why I have to do these steps.. guess more playing around will help later.[/quote]

                                                                                                               

                                                                                                              Are you refering to the Append templates? If so the reason for using them is simply to provide more control over how to populate (and amend if required) the various fields related to, but not always included in, the transaction lines. For example I have reservations about always assuming that a transaction line with a blank G/L Trans code should always have that field filled with whatever the entry was in the last populated record. (The 'Copy from previous ....' feature of Monarch.) On the other hand the BU and RESP fields look like they should always be used for each detail line and the safest way to do that is by using an Append Template. Which means that your next question ...

                                                                                                               

                                                                                                               

                                                                                                              However, I have another question. It seems that the RESP extracted in the detail template do not always tie to the RESP extracted in the append template. When I look at the extracted data in the table view, I spoted many inconsistencies.

                                                                                                               

                                                                                                              EDIT: Example here. The lines above these have BU 34 and RESP 4300. So for Transfer Balance, RESP extracted become 4300 instead of 4710 which is what I expect based on the report. Should I separate the BU and RESP into 2 append templates?[/quote]

                                                                                                               

                                                                                                              ... should probably be answered as 'Yes'. If the BU and RESP field are not always reported together when the RESP field changes (from what you write I assume the BU level may then have several RESP levels below it in the hierarchy), then you may need to use separate templates or, possibly, the 'Copy from previous ....' feature - but see my reservations about that above. You have to consider each field on its merits to decide whether the 'Copy from ...' usage is a safe bet.

                                                                                                               

                                                                                                              HTH.

                                                                                                               

                                                                                                               

                                                                                                              Grant

                                                                                                                • Urgent! newbie needs help
                                                                                                                  joogi _

                                                                                                                  Hey Grant,

                                                                                                                   

                                                                                                                  Just to let you know that I successfully extracted the data from the report right at Jan 1, 2010 12:01am using your instruction earlier  I am not kidding. And I did create 2 appened templates for BU and RESP.

                                                                                                                   

                                                                                                                  The downside of v5 is that I can't even export to Excel as it exceeds 16xxx rows :confused: This is a joke! I can't recall when I used Excel 5 or 8! So I exported to Access, and still have to convert it within Access 2003.. so many unnecessary steps due to the old version of Monarch. But anyways, I did some queries in Access and was able to get close to what I want.

                                                                                                                   

                                                                                                                  Just one more question about the header template: I am not sure why the sub-market name is truncated. I did paint long enough to capture all characters (eg. Sasketchwan), but the extracted data became Sasketc. Did I miss something?

                                                                                                                    • Urgent! newbie needs help
                                                                                                                      Data Kruncher

                                                                                                                      I now have a love/hate relationship with v5.

                                                                                                                       

                                                                                                                      That's the version of the software that got me hooked on this stuff, but the newer releases are so[/B] much better.

                                                                                                                       

                                                                                                                      IIRC, v5 exports to Excel only the number of characters that the Table window is set to display, so you want to autosize your columns before exporting, or just set them wider than you need right now to accomodate future exports.

                                                                                                                       

                                                                                                                      Also, you can overcome the 16,384 row limit imposed when exporting to .xls files by exporting to .csv instead. Excel can still read those just as easily as .xls files, and you'll have all of the records.

                                                                                                                       

                                                                                                                      The only additional advice relates to exporting the .csv files: you'lll likely want to go to the Options->Export and Clipboard dialog. On the bottom of the Text Files tab you'll find a checkbox to Append DOS end-of-file character (x1A). Clear that checkbox, OK the dialog, and save your model.

                                                                                                                        • Urgent! newbie needs help
                                                                                                                          joogi _

                                                                                                                          IIIRC, v5 exports to Excel only the number of characters that the Table window is set to display, so you want to autosize your columns before exporting, or just set them wider than you need right now to accomodate future exports.

                                                                                                                           

                                                                                                                          /QUOTE

                                                                                                                           

                                                                                                                          The name of the provinces are truncated even in the Table window. I dragged the column wider, there are not hidden characters. It seems that that field wasn't extracted completely. odd.. :confused: This is not really a big problem. I can live with it, but was just curious.

                                                                                                                            • Urgent! newbie needs help
                                                                                                                              Grant Perkins

                                                                                                                              The name of the provinces are truncated even in the Table window. I dragged the column wider, there are not hidden characters. It seems that that field wasn't extracted completely. odd.. :confused: This is not really a big problem. I can live with it, but was just curious.[/quote]

                                                                                                                               

                                                                                                                              Check the field definition. The default checks a certain number of rows and (assuming CHARACTER type selected) sets the data width[/B] and display width[/B] accordingly. You may need to change both. Set both to the report size - in this case likely to be the number of dashes under the column headings. (though I did notice that the sample data seems to have at least one 'field' that overlaps left' I assumed something lost {or gained} in the edit.

                                                                                                                               

                                                                                                                              Running the Monarch Verification checker may help here.

                                                                                                                               

                                                                                                                              Well done for achuieving the extraction.

                                                                                                                               

                                                                                                                              Don't rely on my specific direction if you find anomalies that mean the instructions fail. You have the entire report for feedback so will have better information than I have. That's important. Use the ideas and adapt them as necessary - Monarch is often all about being able to use fundamental concepts in creative ways. Once you get into it it is often the source of excellent ideas and analysis that may have previously seemed impossible without excessive effort.

                                                                                                                               

                                                                                                                               

                                                                                                                               

                                                                                                                              Grant

                                                                                                                                • Urgent! newbie needs help
                                                                                                                                  joogi _

                                                                                                                                  Check the field definition. The default checks a certain number of rows and (assuming CHARACTER type selected) sets the data width[/B] and display width[/B] accordingly. You may need to change both. Set both to the report size - in this case likely to be the number of dashes under the column headings. (though I did notice that the sample data seems to have at least one 'field' that overlaps left' I assumed something lost {or gained} in the edit.

                                                                                                                                   

                                                                                                                                   

                                                                                                                                  Grant[/QUOTE]

                                                                                                                                   

                                                                                                                                  The field definition in v5 is probably very different from your version. It doesn't have data width or display width. But I managed to select one of the options available to make it work. I guess the challenge in this whole tutorial, so to speak, is that we are using different versions and some specific functionalities may not be available in v5. That's why at times I had no idea what you're talking about...

                                                                                                                                   

                                                                                                                                  Does the professional edition allow one to open more than one report and combine all the extracted data? What I did was I have to apply the model to the 12 months' reports separately. Each time I have to export the table to Access and append to it's table. One problem I encountered last night was that the database corrupted after appending too many tables to the database? I was wondering if there is any row limit like Excel (old version) since this is only v5? I was hoping that I can export all 12 tables to one single table in Access and do my queries. Assume that there are about 18000 rows of data in each month, multiply by 12, would come to over 200K rows. :eek:

                                                                                                                                    • Urgent! newbie needs help
                                                                                                                                      Grant Perkins

                                                                                                                                      Joogi,

                                                                                                                                       

                                                                                                                                      I think there was a small change between V5 and V6 in terms of field definition screens but you should still be able to set the 2 widths I mentioned. I was checking things in the V6 Training Guide. If one of the menu options somewhere is 'Field List' that should allow you to edit the field attributes.

                                                                                                                                       

                                                                                                                                      The Help file should be installed and should be searchable and offer some guidance.

                                                                                                                                       

                                                                                                                                      Pro offers the oportunity to open 'databases' but as I recall the Standard version should allow you to open multiple reports and extract the data in one go HOWEVER in the Report Window you will only see one report. You should be able to select which report you want to see using the Window menu.

                                                                                                                                       

                                                                                                                                      You should also investigate the menu entry for 'Output Options'. There are usually settings of rdifferent version of target output files - like xls files, and I think the default was for the older style 16k line Excel limit but you should, by the time of V5, also be able to set the 64k line limit. That said if you have a lot more lines you will not be able to run a single export even if you have the latest version of Excel. Monarch will not know about it. (Recent version do, of course, know about MS Office advances.)  Export to Access may be your best option.

                                                                                                                                       

                                                                                                                                      Access does have a file size limit of about 2Gb. How that relates to Monarch's output is a little too complex to try to cover. Check the Help file for 'Specification'. I think it will be there and may help. Technical Info.

                                                                                                                                       

                                                                                                                                      I suspect you may need to export to separate tables (or Access) and then combine in Excel.

                                                                                                                                       

                                                                                                                                      You could also create a batch script if running one report at a time is more reliable. Make the batch run the process multiple times. I can't recall whether V5 is happy with a DOS batch file or required the Autoscript utility provided with older versions of Monarch. The batch option will allow you to run the multiple extracts one after the other just by kicking ioff the batch. (You may know that already of course ...)

                                                                                                                                       

                                                                                                                                      If it will work with a DOS based script (see the help file, with luck) check [URL="http://********************/tips/monarch-batch-file-generator"]this[/URL] out.

                                                                                                                                       

                                                                                                                                      Suffice to say that all would be much more readily available with a new version of Monarch and the cost of a Stand alone licence may be less than the cost of your time working around the obvious constraints of a 10 year old version whilst giving you the opportunity to produce a much slicker result.  Sorry to bring this up again but it has to be mentioned! 

                                                                                                                                       

                                                                                                                                       

                                                                                                                                       

                                                                                                                                      Grant

                                                                                                                                        • Urgent! newbie needs help
                                                                                                                                          joogi _

                                                                                                                                          HI Grant,

                                                                                                                                           

                                                                                                                                          I would like to refer back to the append template for the subtotal line checker that you suggested. I am not sure why I have to paint or highlight all the way up to Account Number from the right. I tried painting all the way to BU and it didn't work.

                                                                                                                                            • Urgent! newbie needs help
                                                                                                                                              Grant Perkins

                                                                                                                                              HI Grant,

                                                                                                                                               

                                                                                                                                              I would like to refer back to the append template for the subtotal line checker that you suggested. I am not sure why I have to paint or highlight all the way up to Account Number from the right. I tried painting all the way to BU and it didn't work.[/quote]

                                                                                                                                               

                                                                                                                                              Hi joogi,

                                                                                                                                               

                                                                                                                                              The purpose of the field is to be able to identify the subtotal rows separately from the base data rows so that you can select them or not select them for filtering purposes. If you keep all lines your grand total values will double.

                                                                                                                                               

                                                                                                                                              Of course if you simply want to convert the report as it is to Excel then you don't really need those selection fields if you can find a way to identify the converted subtotal and totals lines in Excel. That may not be easy if you have already 'filled' the empty fields (Using Copy from previous OR an append template)  through Monarch.

                                                                                                                                               

                                                                                                                                              The long field to the left of the value columns will be empty for subtotal and total rows but not empty for other rows, at least in theory based on what I could see in the sample posted. Creating such a filed manes that you can then select all records where the field is empty, if you want only the totals/subtotals, or all records where the field is NOT empty if you want the base data rows.

                                                                                                                                               

                                                                                                                                              When you say "I tried painting all the way to BU and it didn't work."  - can you provide a little more information about what you did and in what way it did not work?

                                                                                                                                               

                                                                                                                                              Bear in mind that what you are working with in the full report may offer more challenges than I am aware of from the samples posted here on the forum. The ideas I am suggesting are really just that - ideas and techniques. They may work exactly as described or they may need a little local adaptation to be successful. It's quite quick and easy to experiment with Monarch but be sure to keep a saved version of each new model building step so as not to lose everything achieved previously by an injudicious 'save'!

                                                                                                                                               

                                                                                                                                              HTH.

                                                                                                                                               

                                                                                                                                               

                                                                                                                                              Grant

                                                                                                                          • Urgent! newbie needs help
                                                                                                                            Data Kruncher

                                                                                                                            I am really unfamiliar with this[/QUOTE]

                                                                                                                             

                                                                                                                            If I may, [URL="http://********************/extract/video-how-to-build-a-monarch-model"]Building a Monarch Model Video[/URL].

                                                                                                                             

                                                                                                                            With v5 you cannot open a model file first; there must be an open report before applying a saved model.

                                                                                                    • Urgent! newbie needs help
                                                                                                      Grant Perkins

                                                                                                      Hmm.

                                                                                                       

                                                                                                      Based on the sample posted you may gat away with simply trapping the first character of the G/L code column as an Alpha character (or non-blank if some of them start with numeric characters). I assume that all of the detail lines will have a G/L code. Adding a decimal point trap for onne or more of the value columns may also be useful if the report contains unwanted character lines that are caught by the same trap. However be careful that you don't trap the sub-total lines as well should any of those have some sort of title string before them.

                                                                                                       

                                                                                                      Your short detail lines, missing the Acount Number and Description, can probably be filled using the 'Fill from previous line' option in the field definition. If not then treat those fileds as an Append and trap them in their own append template. You may need another append template to grab BU and RESP values if you need them.

                                                                                                       

                                                                                                      1000 pages is a lot to check so you may be wise to run the Verify option to see if Monarch spots any obvious anomalies.

                                                                                                       

                                                                                                      The in-built Help files may prove to be useful for memory jogging if you get slightly stuck on some details.

                                                                                                       

                                                                                                      HTH.

                                                                                                       

                                                                                                       

                                                                                                      Grant