Many Join

5 posts / 0 new
Last post
mgrenick1
Offline
Joined: 03/30/2015 - 10:09
Many Join

Hi
Can anyone help me understand how to perform a many join?
I.e. a primary record in the resulting table for each record in the secondary table?
This is best explained in the pictures below.
Thanks

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi mgrenick1,

For what you want you can use the visual connector, it is located under the Analysis ribbon in the relate section.  So open your first file and then in the visual connector select your second file.  Use the vendor column as your link between the files.  Click on ok and leave it as Matches Only and give it a filename.  You should get the results you are looking for.

I have added screenshots for you.

Brian

Images: 
mgrenick1
Offline
Joined: 03/30/2015 - 10:09

Thanks for this Brian, it was exactly what I was trying to achieve.
I think its worth noting, that there is an IDEA script on the website to peform a cross join i.e. join every record in one table to every record in another - this is a couple of pages of code.
The same result can be achieved with 15 lines - using the above function. By appending a field in both the primary and secondary table with the same value - say "1" and then joining on this field - you will have a cross join in just 15 lines of code.
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Yes, but where would the fun be in thatSmile

Glad it was what you were looking for.

Jiyajijp
Offline
Joined: 12/08/2016 - 06:34

Hi Brian,
While using visual connector Am getting error.
1. Variable not defined Id0. (Then I declared them as Object)
Then got the next error
Object variable or With block variable not set
Please help...below is my code
Function visualConnnectDatabase(resultFileName)
Dim db As Database
Dim task As Object
Dim id0 As Object
Dim id1 As Object
Dim id2 As Object
Set db = Client.OpenDatabase(FileName)
Set task = db.VisualConnector
id0 = task.AddDatabase(FileNameA)
id1 = task.AddDatabase(FileNameB)
id2 = task.AddDatabase(FileNameC)
task.MasterDatabase = id0
task.AppendDatabaseNames = FALSE
task.IncludeAllPrimaryRecords = TRUE
task.AddRelation id0, conditionA, id1, conditionB
task.AddRelation id0, conditionC, id2, conditionD
task.IncludeAllFields
task.CreateVirtualDatabase = False
task.OutputDatabaseName = resultFileName
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function