11 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    data completeness issue

    DM _

      I'm trying to build a model to replace the three separate models that I currently use to extract data from a SAP extract. 

       

      My traps appear to be capturing everything but not all of my data is flowing through to my table.

       

      Can anyone suggest what I might be doing wrong?

       

      Thanks

      DM

        • data completeness issue
          Grant Perkins

          Hi DM and welcome to the forum.

           

          When you mention 'SAP' extract are we looking at a structured report or a pipe ("|") delimited file?

           

          How are you going about defining the templates?

           

          Have you ensured that the field definition s allow for you largest possible fields?

           

          Have you set the field display width to be at least as big as the data size?

           

          Does the extract have a very large number of fields? (Several Hundred for example).

           

          Is there anything unusual about the data you are working with?

           

          Let's see if any of your answers to these questions point us in a direction to investigate.

          • data completeness issue
            DM _

            Hi Grant

             

            In answer to your queries:

             

            1. The extract is pipe delimited.

             

            2. I'm using floating traps, defining the areas of data to capture relative to them.

             

            3. No, but given the nature of the extract if I capture absolutely everything my traps would start to overlap.

             

            4. Yes.

             

            5. Yes, there are thousands of rows of data.

             

            6. The extract is of the SAP ledger codes and which Management reporting codes they feed to.  Where a range of consecutive codes feed to the same Management code you get one row which shows the range followed by subsequent rows of the detailed SAP codes (one per row).  Where only one SAP code feeds a Management code you only get the range (i.e. 100000-100000) without the detailed rows below it.  I'm trying to capture both these types of SAP code.  It's these second type that don't seem to be pulling through.  I'd attach the extract and model for you to see but I can't find an attachment option in the posting screen.  Sorry.

             

            Hopefully this makes sense.

             

            Thanks again

            DM

            • data completeness issue
              Grant Perkins

              Hi DM,

               

              I think you will struggle with this  - even assuming you have successfully changed the pipe to another character for trapping (pipe has a special trap attribute in Monarch and cannot be substituted.)

               

              The normal recommendation for pipe delimited SAP extract is to open them as a 'database' rather than a report.

               

              Floating traps are great but not originally intended for more than one floating trap point really - they can be made to work with two or three fields sometimes. After that you  would have to be very lucky to guarantee a consistently reliable result. (In my opinion - there will be exceptions no doubt and one-off analyses might be OK).

               

              Large number of fields per row could be a problem but unlikley in this case I would think. You could expect Monarch to deal with several million records although the number of fields in those records might have an effect on the absolute top limit. If you find rows missing at the end of the extract but the process seems to have finished without error you might have reached a limit.But you would usually need many more than thousands of rows.

               

              More specifically I think your problem soounds more like a structure issue related to the trap but I doubt the trapping validity on a pipe delimited file anyway.

               

              If you can paso a few lines of sample that would be great. Simple cut the lines form your source file and paste them into the forum using the "Code" button below the entry window to create a tag for the beginning and the end of the pasted section so that the format is retained.

               

              Just click "Code" and and then paste the sample between the two tags. It should then be possible for other to cut the sample lines and create their own file to experiment with.

               

              HTH.

               

               

              Grant

              • data completeness issue
                DM _

                Grant

                 

                I'm not sure it's the pipes that are the issue.  I'm not using them. 

                 

                In trying to answer your previous post I think I've got an explanation.

                 

                The below extract shows the sort of thing I'm dealing with.

                [font="courier"]  |       |   |   |-----BPX 0000481000 - 0000481000 X|X Reserves - Profit and loss account                   

                   

                --   BPX 0000481001 - 0000481003 X

                X                                                      

                 

                                                                                                 

                |       |   |   |   |-----0000481001 Statutory Reserves account for adjustments                            

                  |       |   |   |   |-----0000481002 Amoco S'holders Equity - Retained Earnings                            

                  |       |   |   |   -


                0000481003 Amoco S'holders Equity - Additnal Paid in Cap                         

                /font[/quote]Codes 481001,2,3 are pulling though ok, but the 481000 isn't although it would appear that the relevant range specific trap is capturing them.

                 

                In order to capture all the data I've set up the 481001, etc "singles" template as the detail with the 481000 "range" template as an append, but as I don't have related data for it to be appended to.

                 

                Can you think of a way to overcome this?

                 

                In the mean time I'll try changing the "range" template to the detail with the "singles" to a footer and see how I get on.

                 

                Thanks again

                DM

                • data completeness issue
                  Grant Perkins

                  Originally posted by DM:

                  In order to capture all the data I've set up the 481001, etc "singles" template as the detail with the 481000 "range" template as an append, but as I don't have related data for it to be appended to.

                   

                  Can you think of a way to overcome this?

                   

                  In the mean time I'll try changing the "range" template to the detail with the "singles" to a footer and see how I get on.

                  /b[/quote]Hi DM,

                   

                  If the 481000 line is not trapped as a detail record you won't see it in the table.

                   

                  I'm not at all sure I have got my head around what you need from this report but I think you need to ensure thdetail trap includes 481000.

                   

                  If you then want to use the same selection of data for 481000 as an Append to group the 'family' of codes you can do that as well. Just ensure that the Append trap does not extend beyond the 'right hand' limits of the Detail trap definition.

                   

                  The report layout looks interesting - a fixed width pipe delimited document? Slightly unusual but yes the pipe character will not need to come into play here by the looks of it. Which is probably just as well!

                   

                  HTH.

                   

                  Grant

                  • data completeness issue
                    DM _

                    Grant

                     

                    I think I'm going to have to admit defeat.  I don't see any way to capture all the data in one model.  If I specify the "range" as my detail template then I only capture the first "single" account below that for each "range".  If I specify the "single" lines as my detail trap I don't pull through the "ranges" without any "singles" below them.

                     

                    Thanks for your help.  I think we've advanced my Monarch knowledge a little.

                     

                    Regards

                    DM

                    • data completeness issue
                      Grant Perkins

                      DM,

                       

                      It sounds like you may be trying to use the trap to trap and filter in one. That can be very limiting.

                       

                      However the report looks like it is two (or more?) very different sections so I'm not sure what your output objectives are and why and how the 'singles' become important.

                       

                      I am  tempted to suggest trapping on the account number column and filtering the unrequired lines if necessary.

                       

                      What do you want the output to look like?

                       

                       

                      Grant

                      • data completeness issue
                        DM _

                        Grant

                         

                        Apologies for the delay in responding, I've been on holiday.  I'm going to stop telling my family when I'm on holiday as that's the second time in succession that one of them has taken ill during it.

                         

                        For the sample I need the output to look something like this:

                         

                        481000 Reserves - Profit and loss account

                        481001 Statutory reserves account for adjustments

                        481002 Amoco S'holders Equity - Retained Earnings

                        481003 Amoco S'holders Equity - Additnal Paid in Cap

                         

                        Thanks

                        David

                        • data completeness issue
                          Grant Perkins

                          Originally posted by DM:

                          Grant

                           

                          Apologies for the delay in responding, I've been on holiday.  I'm going to stop telling my family when I'm on holiday as that's the second time in succession that one of them has taken ill during it. /b[/quote]Ah yes, always a good idea to avoid telling people you are on holiday.

                           

                          Originally posted by DM:

                          For the sample I need the output to look something like this:

                           

                          481000 Reserves - Profit and loss account

                          481001 Statutory reserves account for adjustments

                          481002 Amoco S'holders Equity - Retained Earnings

                          481003 Amoco S'holders Equity - Additnal Paid in Cap

                           

                          Thanks

                          David /b[/quote]OK, then you need to get the 481000 line into the table as a detail record it seems. I assume that trap should be possible?

                           

                          Given that the rest of the fields look like they are all in known positions on the line OR, in the case of the first two lines of the sample, the locations can be calculated I would look at simply taking everything after the number as a single field and them slice and dice it accordingly.

                           

                          For the last 3 lines the field is simply the content as it is. For the first two it will be the content after X|X position (if it is fixed it is easy, if it is a variable you need to be able to calculate where it falls and from there, where the description you need will start.

                           

                          Alternatively you could probably use the LSPLIT() function, split on spaces (according to the data) and take what you need after the third space.

                           

                          FOr the second X|X line that would result in a blank field. You don't want that line anyway so if you filter to only include lines where that field is not blank it would be removed form the table displayed.

                           

                          Have a look in the help for the examples for the functions for LSPLIT() (And RSPLIT while you are there!), SUBSTR(), INSTR(), LEFT() and RIGHT(), and a number of the other functions that relate to string manipulation. There is a lot of powerful functionality in there. I would guess that LSPLIT() offers the best approach from the sample but there may be reasons to look in other directions for the real report.

                           

                          HTH.

                           

                           

                          Grant

                          • data completeness issue
                            DM _

                            Grant

                             

                            Thanks for the suggestions.  Unfortunately my lack of skill with floating traps is hampering me somewhat.  Even when I only have one character in my trap, whether that be a "0" or "-" (which to my untrained eye looks exactly the same as the character in the selected row I keep getting the "Floating trap must match the sample line exactly...use the Shift to match sample funtion" error message.  I can't find the Shift to match sample function!

                             

                            And lunch is hours away!!

                             

                            Monday morning blues-ily,

                            DM

                            • data completeness issue
                              Grant Perkins

                              David,

                               

                              Based on your sample data post I'm not sure you need the floating trap - is the account number column consistently placed (as it look in the sample?) and can you just trap on that?

                               

                              The floating trap can be quite tricky of you atempt to use it for multiple positions. You need to use (or create)a sample line that 'describes' the fields you will define at the maximum width possible for each field. Monarch needs to be able to calcualte how to expand (to the max size in the sample line) and contract (to zero size) the data locations between the traps. Any spaces in there and you could be struggling.

                               

                              Not easy to put into words simply I'm afraid. It's a bit like a variation on a delimited report where the safest way to define field sizes would be to use the source database structure definition rather than a sample output because the data in the sample output most likely does not display the full possible width for every field available.

                               

                              Floating traps are very useful for the tasks for which the concept was originally introduced (see the Help example) but can be a huge challenge as complexity is added and the requirement moves beyond the original design intent.

                               

                              Chances are the report is confidential but if you have an option to release it or a good representative test version I would behappy to take a look to see what suggestions I might come up with.

                               

                              HTH.

                               

                               

                              Grant