4 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Cross Tab Question

    s torry

      I need to create an excel spreadsheet with the following column heading - Date, Name 1, Name 2, Name 3, Address Line 1, Address Line 2, City State ZIP, Tax ID, Total, Rep1 #, Rep2 #, Account 1, Account2, etc. The address block is variable and the number of account lines can vary also.

       

      Date: 01/01/01                    Sales Report

      Name & Address                      Account              Amount              Rep

       

      John Jones                             123                    1000.00              001

      123 Main St

      Anytown MA 00000                  333                    5000.00              002

      Tax ID: 111-22-3333

       

                                                 Total                   6000.00

      -


       

      Mary Smith

      John Smith                            457                    2000.00             002

      456 1st St

      Anytown MA 00000                 890                    3000.00             002

      Tax ID: 222-33-3333

       

                                                 Total                   5000.00

       

       

       

      Any suggestions?

       

      Steve

      Monarch Pro V8.02

        • Cross Tab Question
          Grant Perkins

          Hi Steve and welcome to the forum.

           

          Is there any chance you could clarify the report layout for us. It looks a bit unusual as it has posted.

           

          In fact if it really does have Account/Amount/Rep against the Name line and then 2 lines down (or is that address line 2 - same thing or not?) with such close spacing it seems very very unusual.

           

          How many Accounts might there be as a maximum?

           

           

           

          Grant

            • Cross Tab Question
              s torry

              The 1st column has Name, Address and TAX ID, 2nd column has Account Number, 3rd Column has the Amount and the 4th the Rep ID. The total appears under the amount column. I have seen up to 12 accounts listed.

               

              Thank,

               

              Steve

                • Cross Tab Question
                  s torry

                  This may be a little clearer:

                   

                  Date: 01/01/01                    Sales Report

                  Name & Address                      Account              Amount              Rep

                   

                  John Jones                         123                    1000.00              001

                  123 Main St

                  Anytown MA 00000                      333                    5000.00              002

                  Tax ID: 111-22-3333

                   

                                                     Total                6000.00

                  -


                   

                  Mary Smith

                  John Smith                            457                    2000.00             002

                  456 1st St

                  Anytown MA 00000                     890                    3000.00             002

                  Tax ID: 222-33-3333

                   

                                                        Total                   5000.00

                   

                  /code

                    • Cross Tab Question
                      Grant Perkins

                      Hi Steve,

                       

                      Well, a simple looking little report but it does present a few challenges it the requirement is to extract complete records.

                       

                      Lets work backwards from your required output.

                       

                      Date, Name 1, Name 2, Name 3, Address Line 1, Address Line 2, City State ZIP, Tax ID, Total, Rep1 #, Rep2 #, Account 1, Account2, etc.

                       

                      Date:[/B] Simple Append or Page Header template depending on needs.

                       

                      Name1, 2, 3:[/B]

                       

                      How many names might there be?

                       

                      Do multiple names always stack ON TOP of the NAME position that seems to be the default for the record samples posted? (i.e. is the line of the first ACCOUNT always the last or only NAME line?

                       

                      Address Details: /B

                       

                      Should be easy enough to use an address block to process for the address BUT not on one pass ... see below.

                       

                      Tax ID:[/B]

                       

                      Is this always on the line below the address or might it be on the line below the last reported Account?

                       

                      Total:[/B]

                       

                      Looks like it should be a simple footer BUT do you really need the total since, if presented as a summary, each client (Tax ID) can be totaled from the values in the Account details and that would be preferable,  I would suggest, for further analysis.

                       

                      Rep1 #, Rep2 #, Account 1, Account2, etc.[/B]

                       

                      The "etc." bit needs to be considered. How many columns might this create in an Excel export?

                       

                      Does the export need to be consistent from run to run (for instance if the extraction is to be appended to an existing worksheet) or can it simply work with what is found? (Number of columns likely to vary?)

                       

                      If the 'client' has 5 accounts from 3 reps is is important to know which rep relates to which account? If it is that would suggest treating the Rep/Account as a single data entity.

                       

                       

                      There are a number of very different ways one could approach this. The simplest, in terms of Monarch modeling, would be a minimum 2 step approach.

                       

                      The first model would extract the name(s), address and Tax ID and export to a file to be used as a lookup table later. (I am assuming Monarch Pro availability.)

                       

                      A second model would process and extract the Account, Amount and Rep values, each as detail records, and also include the Tax Id for each using an append or footer.

                       

                      The Tax ID would then be the key that links the Name and address information to the detail records and would also allow a method to key a Summary that would offer the output format required. (Although I have some reservations about that format.)

                       

                      However, if the Tax Id data is not consistently positioned in relation to the Account records, especially where there are more than 2 accounts but maybe with Name and address variations as well, it may become difficult to capture consistently for the Account/Amount/Rep model.

                       

                      It looks like the default 'Name' field is consistently found on the first line of the first Account reported. This MIGHT offer an alternative for a link key for the lookup BUT there are risks. Names are not always unique and if multiple names are involved, whilst the uniqueness (connected to a Tax ID) might improve dramatically I'm not sure that the trapping would be 'clean' since it's not obvious to me so far how to pick only the multiple names from the rest of the address in a consistent way. That is not to say it cannot be done but it does not look like a straightforward mapping based on the samples if 100% accuracy needs to be guaranteed.

                       

                      I am wondering whether a more radical approach, involving an interim restructuring of the report to make modeling the final extraction easier, would make more sense here. The answers to my questions, raised above, would still be very relevant to such an approach.

                       

                      HTH.

                       

                       

                      Grant