9 Replies Latest reply: Dec 30, 2011 1:57 PM by John Witherspoon

# How can I compute a field in new table based on columns from two tables

I have two tables:

Table1:

Key     FirstName

1          Some

2          Another

Table2:

Key     LastName

1          One

2          One

And want to compute a third table:

Table 3:

Key FullName

1     "Some One"

2     "Another One"

I've looked at a lot of Join and Load examples, but as far as I can see, none of them computes a field in a new table based on join of 2 other tables.

The actual table1 contains millions of rows, so I would prefer not to create an intermediate table like this

Temp:

Key FirstName   LastName

1     Some          One

2     Another       One

Any suggestions?

Regards,

Rune C

• ###### How can I compute a field in new table based on columns from two tables

Table1:

Key     FirstName

1          Some

2          Another

Table2:

inner join(Table1)

Key     LastName

1          One

2          One

Fistname&' '& LastName as Full name

resident Table1;

drop Table Table1;

hope this helps

• ###### How can I compute a field in new table based on columns from two tables

Thanks, but I believe your Table2 is the intermediate table (Temp:) I'd like to avoid: You're first join-ing the tables, then computing the field.

• ###### How can I compute a field in new table based on columns from two tables

You can take a pivot table

take dimension as  Key

and in expression

Fistname&' '& LastName

label of exp  Full Name

hope this helps

• ###### How can I compute a field in new table based on columns from two tables

It has to be done in load script

• ###### How can I compute a field in new table based on columns from two tables

Dear Rune,

As par my understanding,

if you dont want the joining both the table then concat both field in frontend part.

Ex:

Concat(FirstName&' '&LastName)

Kaushal Mehta

• ###### How can I compute a field in new table based on columns from two tables

Can You show me example with concat()?

This works, but for large tables I (believe I) use to much space -  I guess I should test it...

Table1:

LOAD * INLINE [

Key, FirstName

1, Some

2, Another

];

Table2:

LOAD * INLINE [

Key, LastName

1, One

2, One

];

Join(Table1)

LOAD * resident Table2 ;

Table3:

Load Key, FirstName & ' ' & LastName as FullName Resident Table1;

drop Table1;

• ###### How can I compute a field in new table based on columns from two tables

hi Rune...

i think that what you need its a function call "peek"

i will paste an example where you can see i create a inline and then you can use a "for" to extract values from it and after that create new table as inlines table..

i hope it works for you

im sorry because its in spanish but i think is easy to understand, right now is runnig but with some issue that i cant identify (dicember 30) but i think its what you are looking for

GrupoArchivos:
LOAD * INLINE  [
id_grupo_archivo,grupo_archivo
2,RRHH
4,MANTENIMIENTO
];

let rowcount = NoOfRows('GrupoArchivos');
LET var0 = 0;

for i=0 to rowcount
var0 = peek('id_grupo_archivo', \$(i), GrupoArchivos);

TabA:

LOAD * INLINE [
id_taba,id_grupo_archivo,tipo
1,\$(var0),CENTROS DE INSTRUCCIÓN
2,\$(var0),ACCIDENTES MINFRA
3,\$(var0),ATA];

next i

if its helps you let me know and we can put some work on it...

Saludos desde Venezuela

Feliz Año Nuevo (Happy new year)

• ###### Re: How can I compute a field in new table based on columns from two tables

You could do it with a mapping load.

FirstNameMap:
Key
,FirstName
FROM Somewhere
;
FullNames:
Key
,applymap('FirstNameMap',Key) & ' ' & LastName as FullName
FROM SomewhereElse
;

Edit: Bah, Rob beat me to it.

• ###### Re: How can I compute a field in new table based on columns from two tables

You can use the lookup() function without creatig an intermediate table. However, lookup can be relatively slow. Creating an intermediate table is usually easiest and best. I like the mapping approach like this:

NameMap:

MAPPING LOAD Key, Lastname, TABLE Table2;

Table3:

LOAD Firstname & ' ' & applymap('NameMap', Key) as Fullname RESIDENT Table1;

-Rob