Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
It has to be done in load script
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;
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)
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
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.