Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
javierflorez
Contributor II
Contributor II

Join several similar resident tables

Hi, can I join this N resident tables in another one?

Table_A:
   Ta_Field1,
   Ta_Field2,
   Ta_Field3

Table_B:
   Tb_Field1,
   Tb_Field2,
   Tb_Field3
...
Table_Z:
   Tz_Field1,
   Tz_Field2,
   Tz_Field3

All the N tables have similar names (Table_*) an the fields too (T*_Field1)

Sometimes there are few tables, sometimes there are many. The quantity is not always known.

There's something similar to:

[Table_Total]:

LOAD *

RESIDENT Table_*;

 

Best regards.

 

Labels (1)
4 Replies
saminea_ANZ
Creator
Creator

I am not seeing any common field between each table. If you need forcibly, Do it this way and you can see only Table_A table in the model but allow all tables information.

Table_A:
Ta_Field1,
Ta_Field2,
Ta_Field3;
Join
Table_B:
Tb_Field1,
Tb_Field2,
Tb_Field3;
Join
Table_Z:
Tz_Field1,
Tz_Field2,
Tz_Field3;

Vegar
MVP
MVP

I got the same concerns as @saminea_ANZ  as you have no common fields in your tables. It might be better if you could create a set of data sample tables for us, three tables with a handful rows per table is enough.

 

However I'll give you a tip on how to join multiple tables automatically. The script below is quite inspired and similar to HIC's approach in his blog about the generic load.

 

Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='Table_' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

//Create a empty table in which you can perform the joins upon

   CombinedTable:

   Load * inline [

   DummyField 

];

 

   For each vTableName in $(vListOfTables)

      Left Join (CombinedTable) Load * Resident[$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

Drop field DummyField;

 

Good luck with your kssue8

Brett_Bleess
Former Employee
Former Employee

Hey guys, stupid idea here, what if you aliased the fields dropping the XX_ such that the fields then matched! 🙂  I think that may not be too difficult and should work if those fields are supposed to match up at that level, eh?  The fact I am not as good a developer as you guys is how I come up with this crazy stuff, it is not because I am good at it! 🙂

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Vegar
MVP
MVP

It all demens on what @javierflorez is trying to accomplish?  Is it a join he is looking for or does he just want to stack x amount of tables with the same columns (just with slightly different  names) . If it is the later then your alias approach is quite good as long we he is able to generate it by some mean of logic or by hand.