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

[#1]

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

[SunilChauhan]

Table1:

Key     FirstName

1          Some

2          Another

Table2:

inner join(Table1)

Key     LastName

1          One

2          One

load *

Fistname&' '& LastName as Full name

resident Table1;

drop Table Table1;

hope this helps

[#3]

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.

[SunilChauhan]

after loading the 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

[kaushalview]

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)

   Regrads

Kaushal Mehta

[#6]

It has to be done in load script

[#7]

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;

[#8]

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
    1,ADMINISTRATIVO
    2,RRHH
    3,CONTABILIDAD
    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)

[rwunderlich]

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

[johnw]

You could do it with a mapping load.

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

Edit: Bah, Rob beat me to it.