creating a good data set

[tamirka]

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!

[Greg_Deckler]

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]

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]

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

[tamirka]

hi

i donwt understand your question.

what do you mean with 'In actual life, many people buy same one apartment'?

me and my wife buy this app and she and her mother buy another one.

the difficult part is how do i connect myself with her mother?

[v-huizhn-msft]

Hi @tamirka,

To put it in another way, for the following instance, john and kate buy same 123. Relationship between you and your mother is similar to the relation between john and kate, right?

123,john,123,kate

123,john,123,john

123,kate,123,john

123,kate,123,kate

Best Regards,
Angelia

[tamirka]

yes...

and thats not what i want...