Join (Add) one field from another Table

[deerhunter]

Hope you can help with this, I have two tables,

TABLE1:   

Load * Inline [ 

TableKey, TBL1Code,FieldA,FieldB

TBLKeyA, ValueKeyA, Betty, Lu

TBLKeyB, ValueKeyB, Bob, Ray

TBLKeyC, ValueKeyC, Louie, Lou

];

TABLE2: 

Load *  Inline [

TableKey, TBL2Code,Field3,Field4,Field5

TBLKeyA,  FieldA, AAA, BBB, CCC,DDD

TBLKeyB,  FieldB, BBB, CCC, DDD,EEE

TBLKeyC,  FieldC, CCC, DDD, EEE,FFF

];

I would like to get the field (TBL1Code) from table1 and add it to table2 based off the key (TableKey)

So that i end up with something like this:

TBLKeyA,  FieldA, AAA, BBB, CCC,DDD, ValueKeyA

TBLKeyB,  FieldB, BBB, CCC, DDD,EEE, ValueKeyB

TBLKeyC,  FieldC, CCC, DDD, EEE,FFF, ValueKeyC

[sunny_talwar]

May be like this?

TABLE1:   

Load * Inline [ 

TableKey, TBL1Code,FieldA,FieldB

TBLKeyA, ValueKeyA, Betty, Lu

TBLKeyB, ValueKeyB, Bob, Ray

TBLKeyC, ValueKeyC, Louie, Lou

];

TABLE2: 

Load *  Inline [

TableKey, TBL2Code,Field3,Field4,Field5

TBLKeyA,  FieldA, AAA, BBB, CCC,DDD

TBLKeyB,  FieldB, BBB, CCC, DDD,EEE

TBLKeyC,  FieldC, CCC, DDD, EEE,FFF

];

Left Join (TABLE2)

LOAD TableKey,

  TBL1Code

RESIDENT TABLE1;

But I don't understand what you are trying to do? They will explicitly join together, why do you want them in one table? You want to drop Table1? Then what would happen to the other fields from Table1?

[deerhunter]

actually you are correct i did try that. I do want the other table also. I just need to add the one field from table1 to the other table2 (main fact)  to make my calculations easier. These tables look different in the actual app, i just tried to simulate the challenge sorry for any confusion.

Thank you Sunny you are always a big help

[deerhunter]

Table1 has 60 fields, i would like to add the TBL1Code field to the main fact table(table2) which has 15 fields. In the end you are correct i need both tables to access all the other fields in table1. It makes sense to add the TBL1Code field to main fact table2 to do further calculations since everything else i need is there.

[sunny_talwar]

You have few options.

1) Join

2) Mapping Load with applymap (Don't join - use Applymap instead)

3) Lookup function

[deerhunter]

these are fairly large tables as an additional note. 

[deerhunter]

Both tables are loaded from QVD's