Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

9 Replies
SunilChauhan
Not applicable

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

load *

Fistname&' '& LastName as Full name

resident Table1;

drop Table Table1;

hope this helps

Not applicable

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 (TempSmiley Happy I'd like to avoid: You're first join-ing the tables, then computing the field.

SunilChauhan
Not applicable

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

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
Not applicable

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)

   Regrads

Kaushal Mehta

Not applicable

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

It has to be done in load script

Not applicable

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;

Not applicable

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
    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
Not applicable

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

johnw
Not applicable

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:
MAPPING LOAD
Key
,FirstName
FROM Somewhere
;
FullNames:
LOAD
Key
,applymap('FirstNameMap',Key) & ' ' & LastName as FullName
FROM SomewhereElse
;

Edit: Bah, Rob beat me to it.