topic Re: Join (Add) one field from another Table in App Development

Join (Add) one field from another Table

deerhunter — Mon, 10 Oct 2016 19:47:04 GMT

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

Re: Join (Add) one field from another Table

sunny_talwar — Mon, 10 Oct 2016 20:15:54 GMT

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?

Re: Join (Add) one field from another Table

deerhunter — Tue, 11 Oct 2016 13:05:36 GMT

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

Re: Join (Add) one field from another Table

deerhunter — Tue, 11 Oct 2016 15:03:07 GMT

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.

Re: Join (Add) one field from another Table

sunny_talwar — Tue, 11 Oct 2016 16:05:33 GMT

You have few options.

1) Join

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

3) Lookup function

Re: Join (Add) one field from another Table

deerhunter — Tue, 11 Oct 2016 16:18:16 GMT

these are fairly large tables as an additional note.

Re: Join (Add) one field from another Table

deerhunter — Tue, 11 Oct 2016 16:19:18 GMT

Both tables are loaded from QVD's