5 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Differing Fields in Records

    TonyD _

      :confused: 

       

      Can anyone help? I need to extract the following data but the problem is each record can have a differing number of fields which is not a problem but the differing fields can end up in different places for example if you look at the third field for each record you can see it changes to a different field for each record i.e if you look at record 1 the field named "S/Broker Comm" which is the third field down is named "Government Levy" in record 2.

       

      Record 1

      Gross Prem      :      19,696.25

      Net Premium     :      19,696.25

      S/Broker Comm   :       3,939.25

       

      Record 2

      Gross Prem      :      19,696.25

      Net Premium     :      19,696.25

      Government Levy   :       3,939.2

       

      Record 3

      Gross Prem      :      19,696.25

      Net Premium     :      19,696.25

      C/Agent 1 Comm   :       3,939.2               

       

      Record 4

      Net Premium     :      69,312.50

      S/Broker Comm   :      15,595.31

      Government Levy :       2,772.50

      C/Agent 1 Comm  :         970.37

       

       

      Is there anyway i can extract the information for each record correctly?

       

      Thanks

      Tony

        • Differing Fields in Records
          Nick Osdale-Popa

          Here's a quick rundown of what I would do:

           

          1) select both the value and the description of the third line, using the ":" as a floating trap (assuming you are using V6 and above) (Name them something like Description and Amount, or some such)

           

          2) Select the Gross and Nets as an append

           

          3) You will need to filter out the Gross and Nets out of the table that fall into the Description field.

           

          4) Go into the summary window, select your fields, but on the Description field, go into it's details and select Display Tab and click on Display Values across.

           

          That's pretty much a rough overview, but that may get what you are looking for.

          • Differing Fields in Records
            tcorley _

            This is going to sound odd, but it works

             

            1) Make a detail trap using the first line "Record 1" trap on “Record”, highlight the appropriate info (probably "Record 1"). Label and save the detail trap. You should now see all the Record X lines highlighted.

             

            2) Make an append trap using the 2nd line "Gross Prem : 19,696.25". **This is where things become strange** Make the trap text "Record" (The same as the detail line). Now highlight the info you want (19,6963.25     ) ** extra spaces for longer numbers. Now right click on what you have highlighted. Label the field appropriately and switch to the advance tab. Select Preceding string and fill the text box with "grosss prem". *Don't worry it should all be lower case. Click OK the OK again to save the append. You should now see “Record” and the amount of “Gross Prem” highlighted.

             

            Repeat step 2 "4" more times to get all of the fields:

            Append 2 Preceding String = 'Net Premium' 

            Append 3 Preceding String = 'S/Broker Comm'

            Append 4 Preceding String = 'Government Levy'

            Append 5 Preceding String = 'C/Agent 1 Comm'

             

            *Always using "Record" as the trap

             

            You should now have 1 detail trap and 5 append traps.

             

            Why this works?

            Monarch will look for the 'Preceding String' until it finds and "highlights" it, or comes to a new detail. If the "Preceding string" is not found then the value does not exist in the record and you will see that reflected in your table.

             

            Good Luck...

            • Differing Fields in Records
              Grant Perkins

              Tony,

               

              The tcorley solution sounds like it should work. However, at first sight, this looks like a variation on the solution to your recent 'Multiple Line Trap' question. My first reaction would be that a similar (just slightly different template) solution should work for that though I have not yet had a chance to try it out.

               

              Have you already looked at that possibility and found a problem with it?

               

              Grant

               

              Originally posted by TonyD:

                :confused:  

               

              Can anyone help? I need to extract the following data but the problem is each record can have a differing number of fields which is not a problem but the differing fields can end up in different places for example if you look at the third field for each record you can see it changes to a different field for each record i.e if you look at record 1 the field named "S/Broker Comm" which is the third field down is named "Government Levy" in record 2.

               

              Record 1

              Gross Prem      :      19,696.25

              Net Premium     :      19,696.25

              S/Broker Comm   :       3,939.25

               

              Record 2

              Gross Prem      :      19,696.25

              Net Premium     :      19,696.25

              Government Levy   :       3,939.2

               

              Record 3

              Gross Prem      :      19,696.25

              Net Premium     :      19,696.25

              C/Agent 1 Comm   :       3,939.2               

               

              Record 4

              Net Premium     :      69,312.50

              S/Broker Comm   :      15,595.31

              Government Levy :       2,772.50

              C/Agent 1 Comm  :         970.37

               

               

              Is there anyway i can extract the information for each record correctly?

               

              Thanks

              Tony /b[/quote]

              • Differing Fields in Records
                TonyD _

                Thanks tcorley, i have setup an individual Detail template for each line e.g "Gross Prem", "Net Premium" etc and this seems to be the best solution.

                 

                I did not print the whole file up there because it is so big but i had to create numerous Detail templates for the report. And now i have broken down the file into about 8 different models i can export them into Access and draw up an entity relationship for them.

                 

                Thanks to everyone that responeded.

                 

                Tony

                • Differing Fields in Records
                  Grant Perkins

                  Hi Tony,

                   

                  Just to confirm that I have now had a chance to 'play' with a cut and paste file based on your posted format and it does look possible to extract all of the 'append field', as identified in the tcorley post, in a single Append template.

                   

                  Of course that does not mean to say the real report can be handled the same way. However if it can life may be easier?

                   

                  Grant

                   

                   

                  Originally posted by TonyD:

                  Thanks tcorley, i have setup an individual Detail template for each line e.g "Gross Prem", "Net Premium" etc and this seems to be the best solution.

                   

                  I did not print the whole file up there because it is so big but i had to create numerous Detail templates for the report. And now i have broken down the file into about 8 different models i can export them into Access and draw up an entity relationship for them.

                   

                  Thanks to everyone that responeded.

                   

                  Tony /b[/quote]