Join Multiple Databases

Janik_Muench

Forums

Hi,
 
I have a database with several codes and abbreviations in it. Second thing are all the lists with the full text description, what this codes mean. Now I want to add the text description from the lists to my database. Is there a possible way to join multiple lists in one task? I dont want to create 100 or more databases but the usual join function always creates a new dabase after every join. I found a way, to join multiple fields (with different matching fields) out of one secondary list, but not out of different lists. 
I also added some screenshots of my really basic tries. Screen 1 works perfectly well, 2 will return the error of screen 3.
Hopefully my description is clear enough.
Thanks for your help in advance and please let me know if you need additional input.
Janik

Janik_Muench Wed, 01/22/2020 - 11:37

Hi Brian,
 
thanks for the quick response. Visual connector could work I think. But I still have two little issues to solve then.
 
I need to do 2 connections with the same list in some cases. As an example take the code in my screenshots. I need to add the text for the applied treatment and also the text for the actual used treatment out of the same list. So same field of the secondary file for different linked fields in the primary file. 
 
This would directly lead to the second question is it possible to rename the appended fields? Or do I need to change the headers after I did the complete visual connection with a replace macro? Because at the moment the appended fields will all be named with "Bedeutung" (meaning) and I want to name them with the linked field of the primary file.
 
Thanks for your help
 
Kind regards
Janik

Brian Element Wed, 01/22/2020 - 15:54

Hi Janik, since you need to attach the file twice I would recommend making a copy of the file, just do a direct extraction with no criteria and that will make a copy of the file.  You can then use both files for the connections.  

You should be able to change the field names before doing the connection or you could do it after.

Janik_Muench Thu, 01/23/2020 - 04:12

Hi Brian,
 
thanks again. I just figured out how to do it. I just give a new ID in the script to the same database, this makes it possible to have two connections with one database.
 
I will change the name afterwards in the merged database, because otherwise the macro will have to change the names in to many files. I'll upload my solution here, when its finnished, just to close the topic.
 
Kind regards
Janik

Janik_Muench Thu, 01/23/2020 - 10:25

Hi,
 
so I found the time to create the little script (as attached).
 
I'm running into massive performance problems, when running it. I'm not exactly sure, if it's because of the number of databeses I'm trying to connect or if its because of the similar field names of the "Fieldstoadd", or maybe a combination of both.
 
Regarding the fieldnames:
Like you can see in the script, my attempt was to rename the fields, after the join is created. Do you have any Idea, how I can rename the fields effectively, before the visual connection task is excecuted? I'm hoping, that this will reduce the needed ressources for the task massively.
 
Thanks again.
Kind regards
Janik

Brian Element Thu, 01/23/2020 - 10:43

 

There are a lot of databases so my guess that is causing you performance problems.  You might want to break it down into two or three batches as that might speed things up.

I doubt the rename will have any real performance increases as it is just the name of the field.  You can do the modify fields before the visual connection and use the new field names in there.

Another thing could be you are using the same database several times.  Might be worth seeing if you make copies of them and use the different copies if that spees things up any.

The website encountered an unexpected error. Try again later.