Forum Discussion

tamirka's avatar
tamirka
Advocate II
9 years ago

creating a good data set

hi there,

i have a question' maybe someone has a good idea.

i work in a construction company.

i have a data set with all of our buyers.

the data set looks like this

appartment num , buyer 1 name, buyer 1 ID, buyer 2 name, buyer 2 ID, etc

i want to create connection between buyers.

lets say i bought an appartment with my wife and my wife bought an appartment with her mom.

i want to select me and the report to show me everyone who bought with me and everyone who bought with them (my wife and her mom).

does anyone have an idea how to do this?

thanks!

General Comment
Need Help
Tips and Tricks

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Is there a reasonable maximum to the number of buyers?

     

    I've done some similar things with drug interactions but the data was more in the format of:

     

    patient id,medicine

     

    So, the equivalent in your case would probably be something like:

     

    apartment num, buyer

    123,Joe T

    123,April V

    123,Harvey B

    123,John C

    345 Jimmy V

    345,Carl E

    345,Harvey  B

     

    You should be able to get your data into that format with a pivot/unpivot of the data. Then it would be a matter of creating a Merge Query where you merge the table with itself. You would use both apartment num and buyer 1 as the key when merging and you would essentially get back a table with all the buyers merged with one another in pairs. You might need a central buyers table and a central apartments table as well.

     

    I'd have to model up the data to be sure it could be done this way.

    • tamirka's avatar
      tamirka
      Advocate II
      9 years ago

      hi

      thanks for the reply..

      i have a view that looks like so:

       

      app code, buyer name

      lets take an expample

       

      app code,buyername

      123,john

      123,kate

      123,ron

       

      when i take this view and join it with itself (is that what you meant when you said merge query?) i get

       

      123,john,123,kate

      123,john,123,john

      123,kate,123,john

      123,kate,123,kate

       

      i used the app code as the join parameter

      i also have unique buyers and uniqe app tables.

      i would be happy to follow your lead...

      thanks

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi tamirka,

        Based on your disription, you and your wife buy one department, and your wife and his mother buy other one. How identify the raltionship between them, you and your wife, your wife and her mother.

         

        In actual life, many people buy same one apartment. For example, john, kate and  ron buy 123 app. You select john and the report to show kate and ron buy the same app. If kate and join buy one department rether two departments, how do you identify the relationship? 

        Thanks,
        Angelia Zhang