8 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    Template Help!!

    Dimmickc4 _

      Hello evryone,

      I am new to monach and am having real trouble with the template set up. The report that I am trying to filter can be massive (600 pages!) The data is normally in the same format but each record can have anything from one line of detail to 50.

       

      Its very hard to explain the look of the report, and there is no way of putting a screen print.

       

      The start of each record will have:

      Ê  Branch: 671                     Product: LTS  Currency: HKD  Cluster: LONDON

       

       

      It carries on but I only need the 671 and HKD, This is on Trap line 2.

       

      Next is:

      Nostro Account: 0023655502

      Its on Trap line 3

       

      All the detail will relate to this account, untill the next Account number.

       

      Next:

      GTI  07/08/2009             107,787  NTBQKMZ2

      FXS  07/08/2009           1,338,484

       

      again the info carries on across the line, as you can see the deatail starts the same with 3 alphas 2 Blanks then a date. I have tried setting this trap but it doesnt pick up, it might highlight one line out of 10. I dont understand as from what I can see the set up is the same.

       

      Also in the above example the odd line doesn't line up so the "F" of "FXS" is position 4 where the "G" of "GTI" is in 5. I have tried setting a floating trap but still not working.

       

      If anyone can tell me where im going wrong I would be very grateful.

       

      Thanks

        • Template Help!!
          Grant Perkins

          Hello Dimmickc4 and welcome to the forum.

           

          Are you able to share a sample report with us either by posting a couple of pages (or enough of it to provide a handful of typical records) into the forum or perhaps privately via email? Often this is not possible due to privacy needs or commercial sensitivity but if you can it is really helpful to be able to see the report in its presented format in order to understand how the detail and any append and footer areas work together.

           

          As you have V6 available but are a new user I suspect you will not have had any opportunity to work through the Learning Guide as an introduction to how Monarch does things. The Guide is a huge help. Once things click much of what Monarch can do becomes very easy to acheive but until that point, in tha absence of guidance, even the simplest report can seem to be a challenge. The Guide provides an almost instant understanding for typical users. ( I am assuming that you have not been able to attend any official training courses.)

           

          There is a possibility that you find yourself thrown into the deep end with the report you describe. Anything were a record (or part of a record) can vary from one to 50 lines is likely to be a good candidate for a serious challenge.

           

          Your description of the shifting positions of the lines you wish to capture (which look like detail lines - are they?) suggests there may be an issue with the original report format that may best be dealt with by seeking to tody it up before doing anything else. What is the source of  the report? Some systems embed additional characters in reports that are best removed or marked to be ignored before processing in Monarch, or indeed any other software into which one might want to load them.

           

          Of course it may just be that the report is poorly presented for some reason. Tidying it up first and then creating a model for the cleaner version could be the simplest way forward and is not an unusual step to take to make life easier.

           

          If you can share a sample of the report you can post it here using the CODE tags to retain the format. If you can't post it publically but can share privately send me a Private Message with your email address and I will provide you with my email address to which you can send a sample file.

           

          Hope this helps.

           

           

          Grant

            • Template Help!!
              Dimmickc4 _

              Hi,

              Thank you. Unfortunaly as I work for a large company the programme was just added to my profile with no booklet to help so im kinda just working though it, trying to understand myself. The information isnt really that sensitive so I shall try and put a sample on here.

               

              The report im working with is set in stone really no chance of tidying it up.

               

              Yes, the shifting lines are the main detail of the report.

               

              Ê Program: CR3295                      CMRS Currency position Lates/Back Valued Detail Report                       Page:      A   1   

              Ê  Branch: 671                     Product: LTS  Currency: HKD  Cluster: LONDON BRANCH MAJOR                        Date: 07/08/2009   

              Ê Cut-off Time: 22:00                            Nostro Account: 0019000215            Current Position: OPEN       Time: 00:53:00     

                       VALUE                                              ---  RECEIVED  ---                                                       

              Ê   PROD   DATE                 AMOUNT   REPORT REF. NO.       DATE        TIME     TR STAT        CUSTOMER         TYPE    DETAIL     

              Ê   -


              -


              -


              -


              -


              -


                 -


                -


                 -


                  -


                     

              Ê   GTI  07/08/2009             107,787  NTBQKMZ2 66955757    06/08/2009 22:14:25      L     PRAXAIR INC CBI-        A                 

              Ê   GTI  07/08/2009             619,776  NTBQKMZ3 66955758    06/08/2009 22:14:25      L     PRAXAIR INC MAR         A                 

              Ê   FXS  07/08/2009          27,125,000  22452130HKD06        06/08/2009 23:38:30      C     JPMSL-CASH EQU   LON    A        CAN      

                 FXS  07/08/2009           1,338,484  23027400HKD03        06/08/2009 23:38:30      C     MCTDCH           TOR    A        CAN       

                           TOTAL          29,191,048                                                                               

              Ê                    ADJUSTMENT AMOUNT                                                ADJUSTMENT NARRATIVE                             

                                 -


                                                        -


                              

                ADJUSTMENT TOTAL                   0                                                                               

              FINAL TOTAL          29,191,048                                                                               

              /CODE

               

              in an ideal world I would want the result to be:

              Branch     Currency     Account     Prod     Date     Amount     Reference     Date     TR Stat     Customer     Type

              671     HKD     19000215     GTI     07/08/2009     101787     NTBQKMZ2 66955757     06/08/2009     L     PRAXAIR INC CBI-     A

              671     HKD     19000215     GTI     07/08/2009     619776     NTBQKMZ3 66955758     06/08/2009     L     PRAXAIR INC MAR     A

              671     HKD     19000215     FXS     07/08/2009     27125000     22452130HKD06     06/08/2009     C     JPMSL-CASH EQU   LON     A

              671     HKD     19000215     FXS     07/08/2009     1338484     23027400HKD03     06/08/2009     C     MCTDCH     A

              /CODE

               

              If you would like a larger sample of the report please let me know.

               

              Thanks again.

                • Template Help!!
                  Dimmickc4 _

                  Actually this is a better sample shows a couple of  small recored then large arecord:

                   

                  Ê Program: CR3295                      CMRS Currency position Lates/Back Valued Detail Report                       Page:      A   1   

                  Ê  Branch: 671                     Product: LTS  Currency: AUD  Cluster: LONDON BRANCH MAJOR                        Date: 07/08/2009   

                  Ê Cut-off Time: 22:00                            Nostro Account: 0023967901            Current Position: OPEN       Time: 00:52:48     

                           VALUE                                              ---  RECEIVED  ---                                                       

                  Ê   PROD   DATE                 AMOUNT   REPORT REF. NO.       DATE        TIME     TR STAT        CUSTOMER         TYPE    DETAIL     

                  Ê   -


                  -


                  -


                  -


                  -


                  -


                     -


                    -


                     -


                      -


                         

                  Ê   FTS  07/08/2009             535,000  207844531D           06/08/2009 22:13:51      L     NTFS AS TRUSTEE         A                 

                  Ê   FXS  07/08/2009           2,978,317  23058620AUD04        06/08/2009 23:38:17      C     JPMSL-CASH EQU   LON    A        CAN      

                     FXS  07/08/2009          42,000,000- 23343450AUD03        06/08/2009 23:38:17      C     ABN AMRO BK N.V. SYD    A        CAN       

                   

                  Ê  Branch: 671                     Product: LTS  Currency: CAD  Cluster: LONDON BRANCH MAJOR                        Date: 07/08/2009   

                  Ê Cut-off Time: 16:30                            Nostro Account: 0019012061            Current Position: OPEN       Time: 00:52:48     

                           VALUE                                              ---  RECEIVED  ---                                                       

                  Ê   PROD   DATE                 AMOUNT   REPORT REF. NO.       DATE        TIME     TR STAT        CUSTOMER         TYPE    DETAIL     

                  Ê   -


                  -


                  -


                  -


                  -


                  -


                     -


                    -


                     -


                      -


                         

                  Ê   FTS  06/08/2009               8,274  207844180D           06/08/2009 21:05:13      L     AUTO FX SETTLEME        A                 

                  Ê   FTS  06/08/2009               3,740  207844181D           06/08/2009 21:05:13      L     AUTO FX SETTLEME        A                 

                  Ê   FTS  06/08/2009               3,208  207844182D           06/08/2009 21:05:13      L     AUTO FX SETTLEME        A                 

                     FTS  06/08/2009               1,668  207844184D           06/08/2009 21:05:14      L     AUTO FX SETTLEME        A                  

                   

                  Ê Program: CR3295                      CMRS Currency position Lates/Back Valued Detail Report                       Page:      A   1   

                  Ê  Branch: 671                     Product: LTS  Currency: CHF  Cluster: LONDON BRANCH MAJOR                        Date: 07/08/2009   

                  Ê Cut-off Time: 13:00                            Nostro Account: 0019000777            Current Position: OPEN       Time: 00:52:49     

                           VALUE                                              ---  RECEIVED  ---                                                       

                  Ê   PROD   DATE                 AMOUNT   REPORT REF. NO.       DATE        TIME     TR STAT        CUSTOMER         TYPE    DETAIL     

                  Ê   -


                  -


                  -


                  -


                  -


                  -


                     -


                    -


                     -


                      -


                         

                  Ê   STS  06/08/2009              19,501  52700WHL5T           06/08/2009 13:03:22      L     PANTHER CDO V B  LON    A                 

                  Ê   CER  06/08/2009          89,998,271- ISD6710000277928     06/08/2009 13:06:41      L     *** UNKNOWN ***         A                 

                  Ê   FTS  06/08/2009              21,588  207839877D           06/08/2009 13:19:44      L     VISTAPRINT N BV         A                 

                  Ê   FTS  06/08/2009           4,439,487  207839891D           06/08/2009 13:19:45      L     CARGILL GFP             A                 

                  Ê   FTS  06/08/2009              26,358  207840239D           06/08/2009 13:42:04      L     AUTO FX SETTLEME        A                 

                  Ê   FXS  06/08/2009         203,000,000  23922230CHF01        06/08/2009 13:42:13      L     UBS AG           ZUR    A                 

                  Ê   FTS  03/08/2009           9,514,000- 416201368D           06/08/2009 14:26:15      C     BANCA IMI SPA           A        CAN      

                  Ê   FTS  06/08/2009              17,060  207841059D           06/08/2009 14:38:03      L     G52792                  A                 

                  Ê   FXS  06/08/2009          53,095,400- 24048450CHF02        06/08/2009 14:39:14      L     COMMERZBANK AG   FFM    A                 

                  Ê   FTS  04/08/2009                  52  207841414D           06/08/2009 14:50:11      L     MCS OUR PAYMENT         A                 

                  Ê   FTS  22/06/2009                  75- 207842171C           06/08/2009 15:30:59      L     PALOMA SECURITIE        A                 

                  Ê   FTS  22/06/2009                  75  207842171C           06/08/2009 15:56:53      C     PALOMA SECURITIE        A        CAN      

                  Ê   FTS  22/06/2009                  75- 207842171C           06/08/2009 15:56:53      L     PALOMA SECURITIE        A                 

                  Ê   GTI  04/08/2009             263,218- C3137930 66935327    06/08/2009 17:00:48      L     SSP DEAM GLOB TH        A                 

                  Ê   FTS  06/08/2009             151,584  207843141D           06/08/2009 17:01:41      L     AUTO FX SETTLEME        A                 

                  Ê   GTI  04/08/2009             379,834- C3137932 66935430    06/08/2009 17:03:13      L     SCPF RE DEAM            A                 

                  Ê   GTI  04/08/2009             133,304- C3137931 66936947    06/08/2009 17:23:01      L     SOCPF RE DEAM           A                 

                  Ê   LNS  04/08/2009                 200  540988357            06/08/2009 17:33:54      L     AB ACQUISITIONS         A                 

                  Ê   LNS  04/08/2009               5,355  540988363            06/08/2009 17:33:54      L     AB ACQUISITIONS         A                 

                     TAX  06/08/2009              25,864  764355 66955504      06/08/2009 22:11:02      L     *** UNKNOWN ***         A                  

                   

                   

                  /CODE

                    • Template Help!!
                      Grant Perkins

                      OK, this is a fairly standard report to deal with EXCEPT that it is encoded as UTF-8 rather than ASCii or ANSI and as I recall Version 6 only offers ASCii or ANSI as one of the INPUT OPTIONS.

                       

                      Using a later version ans setting UTF-8 as the input option the offset lines are no longer offset and all aligns very nicely.

                       

                      You don't by any chance have an option to use  a later version of Monarch do you? I think it may have been V8 where the encoding options were enhanced.

                       

                      Putting that aside for for a moment ....

                       

                      Your detail records are single line records for the transactions  - the FTS, FXS , etc., lines These are the ones with the shifting data problem. The logical trap seems to be the "/" characters in the first date although that will not work currently for the shifted lines. We need to deal with the shift as a separate issue in a moment if you only have access to Version 6.

                       

                      The other fields you require are either 2 appends OR, perhaps better, a single multi-line append. In fact I would be tempted to create a single append type with 5 lines in the sample starting at the "Branch:" line and ending with the dashed line that indicates the field positions and widths. You should than be able to piock out the field you want from that block. I am assuming that the section header is a constant format.

                       

                      There is a possibility that page breaks may cause some issues (there always is!) but from the sample I am hoping that is not the case for this report. It doen't look like the content seen so far would be adversely affected by an Page Break but you never know! We can deal with that if a problem appears.

                       

                      The Offset lines.

                       

                      Basically we have two options if you are stuck with V6. I don't think you will have reliable success with the Floating trap as it stands - not without some black magic processing at least - so the better objective would be to 'fix' the anomaly in the report.

                       

                      I'll let you digest the information here whilst I check the options for fixing!

                       

                       

                      HTH.

                       

                       

                      Grant

                        • Template Help!!
                          Dimmickc4 _

                          Hi,

                          I have something called Monarch CB 10 or Monarch 8 available, might take a couple of days to get though.

                            • Template Help!!
                              Grant Perkins

                              If CB 10 is a typo for V10 then go for that as it will not only make life a lot easier for this report but adds a great deal more functionality that you should find useful immediately - like automatically suggesting traps for the data you indicate you want to extract.

                               

                              I think I'm right in saying that V8 also offers the UTF-8 encoding type that will resolve the offset issue as well so go for that if you are in a hurry and can get that sooner.

                               

                              Alternatively using V6 there is a stand alone program that was included with the distributions (or can be downloaded from the Datawatch Website still) that aloows you to tweak the files before loading into Monarch. Indeed there a two or three but the one you want here is called MSRP.EXE and will allow you to preprocess the report to remove the spurious (for your purposes) character at the first position thus making all the detail lines align with each other.

                               

                              MSRP stands for Monarch String Replacement Program. (In later versions this functionality has been included in the Monarch Utility.)

                               

                              There are other similar programs available via the internet or the task can be performed (with care) using an editor.

                               

                              The idea is to change a unique character string - in this case the spurious first column character plus 3 spaces - into something more helpful, just three spaces will do here.

                               

                              The only downside is if you are going to be analysing this report regularly since you will need to run the tweak befor each analysis of new versions of the report. The ability to set the Encoding method in the later version of Monarch would make this unnecessary.

                               

                              If you do need to run using MSRP now I can help you with the command set up you will need.

                               

                              HTH.

                               

                               

                              Grant

                                • Template Help!!
                                  Dimmickc4 _

                                  I think I may have managed to do it, I little sloppy but I put the detail onto floating trap and just increased the size of the field to capture. May need a little work when exported but seems to be a temporary solution.

                                   

                                  Thanks you so much for your help.

                                    • Template Help!!
                                      Grant Perkins

                                      If you use V6 and the MSRP program (it runs in seconds) you should be able to get a perfect file for processing exactly how you want to export it and save the messing about afterwards.

                                       

                                      With V10 (or possiibly even V8 if I remember correctly) you would only need to set one of the Input Options from the menu and that would be saved as part of the model.

                                       

                                       

                                      Grant