1 Reply Latest reply: Sep 18, 2012 3:46 AM by whiteline _ RSS

    Data model/Self join

      Hello.

       

       

      I think some kind of self-join something might work here but this issue is driving me crazy.

       

      I have a list of companies (Companies) and various tests have been performed on them (by another dept) - in my example, if the company name is similar to any other in the table (test1) it FAILS, and ifthe phone number similar (test2) it fails too.

       

      I also have a 'Results' table which links matching companies into groups. So Apple and Apple Inc are deemed similar, and fail the test. The lookup table links them together (the design of this table is not set in stone and can be changed.) I will have many more tests eventually.

       

       

      I want to be able to display a complete list of all my companies, show wether they pass or fail the tests, and if they fail, to be able to click the company name and see a list of companies which are linked on the fail.

       

      Attached example should make a bit more sense, although my data model is down the wrong path (and doesnt work anyway!)

       

      Im thinking some kind of self join on the Results table perhaps? Would love some advice.

       

      thanks

        • Re: Data model/Self join
          whiteline _

          Hi.

          You can just use aggregation with Group by and Count in script (if you want).

          Or you can create a simple Chart with corresponding dimension and just test if Count()>1.

           

          Of course  you should do this after applying a map to group your similar companies (search for applymap in help).