8 Replies Latest reply: May 15, 2014 10:09 AM by TMERRYMAN _ RSS

    Comparing values within a field for common values in another field

    TMERRYMAN _

      Disregard, I am going another route with this because I don't think this will work...

       

      I need to compare data within a field with common values for another field.  For instance, in my sample, I need to compare the field: Location Exempt#-Trimmed for each location #0201.  Is this possible? 

       

      My report:

      CUSTOMER NO:  0001121      NAME:  

      -


               

          ADDRESS:   SORT FIELD:  COLL 1                                                                               

      CITY:                         TEMPORARY CUST?:  N                 

          COUNTRY:  USA United States of America        OPEN ITEM CUST?:  Y                 

            STATE:  TX        ZIP CODE: 78469           STATEMENT CYCLE:  X                 

         PHONE NO:  (361)          EXT:         PRINT DUN MSSG?:  Y                 

              FAX:  (361)    BATCH FAX: N         CUSTOMER TYPE:  FLCC              

          CONTACT:                                                                               

      EMAIL:                                                                               

      URL:                                                                          

      SHIP METHOD:                                                                          

         SCHEDULE:  TX VICVIC                               PRICE LEVEL:  C                 

          SHIP-TO:                                        DISCOUNT RATE:      .000%         

      TERMS CODE:  30   Net 30 days                    FIN CHARGE RATE:      .000%         

      SALESPERSON:  0000 House Account                                                      

         SALES CD:                                         CREDIT LIMIT:         .00        

          COMMENT:                                      ON CREDIT HOLD?:  N                 

      DFLT PMT TYP: NONE                                                                    

      C-STORE LOC:  N                                                                       

      -


               

      TAX CODE:  TX                 EXEMPTION NUMBER: 1-74-1649707-5                        

      TAX CODE:  TX VIC             EXEMPTION NUMBER: 1-74-1649707-5                        

      TAX CODE:  TX VICVIC          EXEMPTION NUMBER: 1-74-1649707-5                        

      -


               

      LOCATION: 0201                              PRIMARY LOCN: N            

         ADD.1: **PO REQUIRED**                PHONE:                   EXT:              

         ADD.2:                                    FAX:                 BATCH: N            

         ADD.3:                                CONTACT:                                     

          CITY: 0 - 25 Miles                   SLSPRSN: 1002 Laura Varela                   

         STATE: TX ZIP: FR025             TAX SCHEDULE: TX CAMSAN                           

                                             WAREHOUSE: W02 RGV Warehouse                   

          TAX CODE:  TX                 EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX CAM             EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX CAMSAN          EXEMPTION:  1-74-1649707-5                                                                               

      LOCATION: 0202   PRIMARY LOCN: N            

         ADD.1: **PO REQUIRED AT DELIVERY**    PHONE: (956) 702-0066    EXT:              

         ADD.2:                                    FAX:                 BATCH: N            

         ADD.3:                                CONTACT:                                     

          CITY: 26 - 50 Miles                  SLSPRSN: 1024 Cassie Bounds                  

         STATE: TX ZIP: FR050             TAX SCHEDULE: TX CAMSAN                           

                                             WAREHOUSE: W02 RGV Warehouse                   

          TAX CODE:  TX                 EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX CAM             EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX CAMSAN          EXEMPTION:  1-74-1649707-5                                                                               

      Run Date: 11/03/2010  TM       Thomas Petroleum, LLC            Page: 2               

      A/R Date: 11/03/2010             CUSTOMER LISTING               Time: 09:53 AM        

                                  PERMANENT CUSTOMERS ONLY                                                                               

      CUSTOMER NO:  0001121      NAME:  Nueces Power Equipment                              

      -


                                                                                       

      LOCATION: 1001  PRIMARY LOCN: N            

         ADD.1:  PHONE: (361)  EXT:              

         ADD.2:                                    FAX:                 BATCH: N            

         ADD.3:                                CONTACT:                                     

          CITY: 0 - 25 miles                   SLSPRSN: 1029 Nicki Martin                   

         STATE: TX ZIP: FR025             TAX SCHEDULE: TX VICVIC                           

                                             WAREHOUSE: W01 Victoria Warehouse              

          TAX CODE:  TX                 EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX VIC             EXEMPTION:  1-74-1649707-5                          

          TAX CODE:  TX VICVIC          EXEMPTION:  1-74-1649707-5                          

      -


               

      /CODE

       

      In table view:

      CustNumber     CustomerName     Location     LocTaxSchedule     Whse     LocationTaxCode-Trimmed     LocationExempt#-Trimmed     CustTaxCode     CustExempt#

      0001121     Nueces       0201     TX CAMSAN     W02     TX     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       0201     TX CAMSAN     W02     TX CAM     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       0201     TX CAMSAN     W02     TX CAMSAN     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       0202     TX CAMSAN     W02     TX     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       0202     TX CAMSAN     W02     TX CAM     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       0202     TX CAMSAN     W02     TX CAMSAN     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       1001     TX VICVIC     W01     TX     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       1001     TX VICVIC     W01     TX VIC     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001121     Nueces       1001     TX VICVIC     W01     TX VICVIC     1-74-1649707-5     TX VICVIC     1-74-1649707-5

      0001127     American  Inc     0010     TX SMITYL     W08     TX     1-57-0541745-7     TX CAMSAN     1-57-0541745-7

      0001127     American  Inc     0010     TX SMITYL     W08     TX SMI     1-57-0541745-7     TX CAMSAN     1-57-0541745-7

      0001127     American  Inc     0010     TX SMITYL     W08     TX SMITYL     1-57-0541745-7     TX CAMSAN     1-57-0541745-7

      /CODE

        • Comparing values within a field for common values in another field
          Olly Bond

          Hello TMerryman,

           

          It's hard to tell from a small sample which is the best approach, but generally I'd make a summary with key fields Customer and Location and Trimmed, and Count() as the only measure. Set the Trimmed field to be displayed across and not down. If this gives you too wide a data set, promote Trimmed and set Location to be across instead.

           

          You can use a two pass solution to make this a little more sophisticated, and show the values in the grid instead of just a count of how many times they appear.

           

          Best wishes,

           

          Olly

            • Comparing values within a field for common values in another field
              TMERRYMAN _

              Hello TMerryman,

               

              It's hard to tell from a small sample which is the best approach, but generally I'd make a summary with key fields Customer and Location and Trimmed, and Count() as the only measure. Set the Trimmed field to be displayed across and not down. If this gives you too wide a data set, promote Trimmed and set Location to be across instead.

               

              You can use a two pass solution to make this a little more sophisticated, and show the values in the grid instead of just a count of how many times they appear.

               

              Best wishes,

               

              Olly[/QUOTE]

               

              Olly, this approach wasn't working for what I needed.  I am going to try your next suggestion on removing the page headers.

            • Comparing values within a field for common values in another field
              TMERRYMAN _

              This model works well except for when the Tax Code for a customer or location breaks across the page.  What makes it tricky is, other than the 3-line header beginning with "Run Date", there is also a header on each page carrying over the customer number and name.  This line is getting in the way of my Field for Tax Codes and Tax exemption numbers.  I have those fields ending on left justification and then using a calculated field with the textline function to pull the lines I need.  I include an IF function to filter out any lines that begin with a "-".  Using this method, the field ends on the first line for Tax Code and Exemption number when it falls across pages.  There should be three lines generally.  Does anyone have a suggestion for how to get around this problem?

               

               

               

              Run Date: 11/01/2010  TM       TP, LLC            Page: 7102            

              A/R Date: 11/01/2010             CUSTOMER LISTING               Time: 04:09 PM        

                                          PERMANENT CUSTOMERS ONLY                                                                               

              CUSTOMER NO:  0012098      NAME:  - LaGrange BB                              

              -


                                                                                               

              LOCATION: PRIMARY LOCN: N            

                 ADD.1:                                  PHONE:      EXT:              

                 ADD.2:                                    FAX:                 BATCH: N            

                 ADD.3:                                CONTACT: BOB Bob                             

                  CITY: 76 - 100 Miles                 SLSPRSN: 0000 House Account                  

                 STATE: TX ZIP: FR100             TAX SCHEDULE: TX FAY                              

                                                     WAREHOUSE: W03 LaGrange Warehouse              

                  TAX CODE:  TX                 EXEMPTION:  12505279252                             

                  TAX CODE:  TX FAY             EXEMPTION:  12505279252                                                                               

              LOCATION: 3494 Tait  PRIMARY LOCN: N            

                 ADD.1:                                  PHONE: ( EXT:              

                 ADD.2:                                    FAX:                 BATCH: N            

                 ADD.3:                                CONTACT: CLEARENCE Clearence                 

                  CITY: 51 - 75 MIles                  SLSPRSN: 0000 House Account                  

                 STATE: TX ZIP: FR075             TAX SCHEDULE: TX FAY                              

                                                     WAREHOUSE: W03 LaGrange Warehouse              

                  TAX CODE:  TX                 EXEMPTION:  12505279252                             

                  TAX CODE:  TX FAY             EXEMPTION:  12505279252                                                                               

              LOCATION: 5059 SheridanGasP/75060,75059,74470              PRIMARY LOCN: N            

                 ADD.1:                                  PHONE:                   EXT:              

                 ADD.2:                                    FAX:                 BATCH: N            

                 ADD.3:                                CONTACT:                                     

                  CITY: 26 - 50 Miles                  SLSPRSN: 0000 House Account                  

                 STATE: TX ZIP: FR050             TAX SCHEDULE: TX FAY                              

                                                     WAREHOUSE: W03 LaGrange Warehouse              

                  TAX CODE:  TX                 EXEMPTION:  12505279252                             

                  TAX CODE:  TX FAY             EXEMPTION:  12505279252                             

              -


                                                                                               

              CUSTOMER NO:  0012099      NAME:   - Victoria BB                              

              -


                       

                  ADDRESS:               SORT FIELD:  BUYBACK           

                            Post Office Box R                                                       

                     CITY:  Concord                             TEMPORARY CUST?:  N                 

                  COUNTRY:  VIC Victoria - TX                   OPEN ITEM CUST?:  Y                 

                    STATE:  CA        ZIP CODE: 94524           STATEMENT CYCLE:  .                 

                 PHONE NO:                         EXT:         PRINT DUN MSSG?:  Y                 

                      FAX:                   BATCH FAX: N         CUSTOMER TYPE:  BUYB              

                  CONTACT:                                                                               

              EMAIL:                                                                               

              URL:                                                                          

              SHIP METHOD:                                                                          

                 SCHEDULE:  TX VICVIC                               PRICE LEVEL:  Z6                

                  SHIP-TO:                                        DISCOUNT RATE:      .000%         

              TERMS CODE:  10   Net 10 days                    FIN CHARGE RATE:     1.500%         

              SALESPERSON:  1001 Clifford Goebel                                                    

                 SALES CD:                                         CREDIT LIMIT:         .00        

                  COMMENT:  BUYBACK                             ON CREDIT HOLD?:  N                 

              DFLT PMT TYP: NONE                                                                    

              C-STORE LOC:  N                                                                       

              -


                       

              TAX CODE:  TX                 EXEMPTION NUMBER: 12505279252                                                                               

              Run Date: 11/01/2010  TM       Thomas Petroleum, LLC            Page: 7103            

              A/R Date: 11/01/2010             CUSTOMER LISTING               Time: 04:09 PM        

                                          PERMANENT CUSTOMERS ONLY                                                                               

              CUSTOMER NO:  0012099      NAME:  Exterran - Victoria BB                              

              -


                       

              TAX CODE:  TX VIC             EXEMPTION NUMBER: 12505279252                           

              TAX CODE:  TX VICVIC          EXEMPTION NUMBER: 12505279252                           

              -


                       

              LOCATION: 1001                          PRIMARY LOCN: N            

                 ADD.1:                                  PHONE:                   EXT:              

                 ADD.2:                                    FAX:                 BATCH: N            

                 ADD.3:                                CONTACT:                                     

                  CITY: 76 - 100 Miles                 SLSPRSN: 1029 Nicki Martin                   

                 STATE: TX ZIP: FR100             TAX SCHEDULE: TX VICVIC                           

                                                     WAREHOUSE: W01 Victoria Warehouse              

                  TAX CODE:  TX                 EXEMPTION:  12505279252                             

                  TAX CODE:  TX VIC             EXEMPTION:  12505279252                             

                  TAX CODE:  TX VICVIC          EXEMPTION:  12505279252                                                                               

              LOCATION: 1002 Louise / 74236-5                            PRIMARY LOCN: N            

                 ADD.1:                                  PHONE:                   EXT:              

                 ADD.2:                                    FAX:                 BATCH: N            

                 ADD.3:                                CONTACT:                                     

                  CITY: 51 - 75 MIles                  SLSPRSN: 1029 Nicki Martin                   

                 STATE: TX ZIP: FR075             TAX SCHEDULE: TX VICVIC                           

                                                     WAREHOUSE: W01 Victoria Warehouse              

                  TAX CODE:  TX                 EXEMPTION:  12505279252                             

                  TAX CODE:  TX VIC             EXEMPTION:  12505279252                             

                  TAX CODE:  TX VICVIC          EXEMPTION:  12505279252                                                                               

              /CODE

                • Comparing values within a field for common values in another field
                  Olly Bond

                  Hello TMerryman,

                   

                  Having multiple instances of "page header" data can be a pain, but as long as your lines are less than 255 characters long, it's fairly easy.

                   

                  Open the report, trap every line with a null trap, select the whole line as one field. That gets everything into the table looking just like the report. Use a table filter to exclude lines starting with "Run Date", "A/R Date" or containing "PERMANENT CUSTOMERS ONLY". Export the filtered table as fixed width text, and start again.

                   

                  This time round, you don't have two page headers to deal with, just one, so you can define a page header template and use a multi-line detail to handle data that breaks over this.

                   

                  HTH,

                   

                  Olly

                    • Comparing values within a field for common values in another field
                      TMERRYMAN _

                      Hello TMerryman,

                       

                      Having multiple instances of "page header" data can be a pain, but as long as your lines are less than 255 characters long, it's fairly easy.

                       

                      Open the report, trap every line with a null trap, select the whole line as one field. That gets everything into the table looking just like the report. Use a table filter to exclude lines starting with "Run Date", "A/R Date" or containing "PERMANENT CUSTOMERS ONLY". Export the filtered table as fixed width text, and start again.

                       

                      This time round, you don't have two page headers to deal with, just one, so you can define a page header template and use a multi-line detail to handle data that breaks over this.

                       

                      HTH,

                       

                      Olly[/QUOTE]

                       

                      I thought this was going to work but I hit another snag.  The "page header" with the customer number and name is not always a page header.  It is actually data that I need for an append template.  If I make it a page header, it makes ALL of the customer number/name lines as page headers.  I only need the first instance of it, but I don't see a way to trap it.

                        • Comparing values within a field for common values in another field
                          Olly Bond

                          Hello TMerryman,

                           

                          There's a trick, but it's been a long day and I'm not sure I can explain it very easily. It involves the approach of trapping every single line, but then you have an intermediate stage of a summary using customer level appends as a hidden key column. (I've used this extensively in various hacks to parse XML model and project files by generating batch commands.)

                           

                          Would you be able to email me a sample report, and I'll happily have a bash at creating some models for you?

                           

                          Best wishes,

                           

                          Olly