6 Replies Latest reply on Sep 14, 2012 9:15 AM by surfingmussel

    Compare Values Table "1" to Table "2"

    surfingmussel

      Sounds simple, but the solution is escaping me.

       

      Table 1 - "FY2011" has the following values in Field:"Account"

      A

      B

      C

      D

       

      Table 2 - "FY2012" has the following values in Field:"Account"

      B

      C

      D

      E

       

      What is the simplest way to automatically (script, portal, whatever) to show that "in FY2011 there was account A that doesn't appear in FY12; and in FY12 there was account E that didn't appear in FY11"?

       

      Essentially I want to ignore the "overlapping" accounts "B,C," and call out accounts "A,B".

       

      I know this isn't rocket surgery, but I am at a loss. If its easier if all accounts are in the same table (with separate field identifying the Fiscal Year), I am cool with that, also.

       

      Thanks!

       

      - Doug

        • 1. Re: Compare Values Table "1" to Table "2"

          Hi Doug,

           

          You can relate the tables as Table1::account = Table2::Account

           

          Then in table1 perform a script of:

           

          Perform Find [ Table2::account  ... and just type * ... meaning find all Table1 records with a matching account in table 2. ]

          Show Omitted Only ... meaning show those without match

           

          ... will give you records in table 1 which aren't in Table2.  Reverse to table2 layout, rinse and repeat in table 2.

           

          Depending upon how you use the information and whether this is a one-time thing or on-going process, you can colorized accounts without a match also.  The best approach would depend upon your next steps with the information.  :-)

          • 2. Re: Compare Values Table "1" to Table "2"
            surfingmussel

            Thank you very much!!!

            • 3. Re: Compare Values Table "1" to Table "2"

              I assume this is a one-time data migration or cleanup project?  In general, accounts should be in same table using year field or other field such as category to segregate them.  Let us know if there are other questions.  There are many great folks here ready to assist.  :-)

              • 4. Re: Compare Values Table "1" to Table "2"
                surfingmussel

                Thanks...if they are in the same field (one table as suggested), how would I modify your solution to solve the same problem...a "double find" looking for account number AND fiscal year, omitting found set also?

                 

                - Doug

                • 5. Re: Compare Values Table "1" to Table "2"

                  I am not sure I understand, Doug.  I am suggesting that, in general, all accounts should be in the same table.  But if you have different years (which also contains information specific to the year) then you should have a related table for the year data (which is multiple to the one account).   If you have accounts in table 2 with Account Number 1002 and Year 2011 and another record with Account Number 1002 and Year 2012 and you want to know if table 1 has Account Number 1002 with Year 2011, then include the year in the relationship to restrict the matches to also matching on year. 

                   

                  Can you explain more what you are attempting to achieve and your current situation and what the tables represent?  Is one the General Ledger and the other LineItems for example or are they both an Accounts table that should be merged or ??  Hang in there ... we'll walk you through it.

                   

                  BTW, you can join any two table occurrences - even join a table occurrence to itself by simply duplicating it in the graph (known as a self-join).  So if all the records were in the same table, you can still create the relationship to 'filter' your results.

                   

                  Message was edited by: LaRetta

                  • 6. Re: Compare Values Table "1" to Table "2"
                    surfingmussel

                    I think that I'm on the right track now...I was messing around with the "self join" and, along with your first suggestion (use of find omitted) I can get where I need to be.

                     

                    I was over-complicating things and you got me on the right track. I continue this post if I get "locked up" again.

                     

                    Thanks again for your help!!!

                     

                    - Doug