Concatenating 2 tables and using a 3rd one to fill some info

[camilo]

Hi, I´m trying to create from 3 tables one table wich has all the information, but I have had some problems, let me explain it with an example and maybe someone can help me

I have 3 tables like this:

TABLE  1
A	Name	ID
aaa	Hugo
aaa	Paco
aaa	Luis

TABLE  2
A	Name	ID
aaa		1
aaa		1
aaa		2
aaa		3
aaa		3

TABLE  3
Name	ID
Hugo	1
Paco	2
Luis	3

I´m concatenating TABLE1 and TABLE2 hopping to get TABLE5 but Im getting TABLE 4:

TABLE  4
A	Name	ID
aaa	Hugo
aaa	Paco
aaa	Luis
aaa		1
aaa		1
aaa		2
aaa		3
aaa		3

TABLE  5
A	Name	ID
aaa	Hugo	1
aaa	Paco	2
aaa	Luis	3
aaa	Hugo	1
aaa	Hugo	1
aaa	Paco	2
aaa	Luis	3
aaa	Luis	3

What I´m doing wrong?

If I´m not being very clear please ask.

Thanks

[swuehl]

Hi camilo,

if you concatenate tables you just add rows (that's what you got with table4, table2 appended to table1), you don't mix content from table1 with table2 in a specific row or fill missing values ( that's what you apparently assumed to get)

It seems to me that you want to use table3 as lookup table for both table1 and table2, filling the missing column values in each row with the appropriate values,

right?

You could do that with lookup() function, please have a look at my attachment (you need to open table viewer to actually see that the resulting table is like table5.

You probably can do that also with joins, but I leave that for someones else's exercise. I think (if my assumption of what you want is correct) that using lookup function is more intuitive in that case.

Regards,

Stefan

[#3]

Hi Camilo,

It's not clear for me what you want exactly.

Please let me know how looks the final table. I'm confused because in Tabel5 we don't have unique informations. For example name Hugo is for three times, Luis the same, etc.

Best Regards.

[camilo]

In deed Swuel you are right, how can I use the table wiever to see you example right?

I´m trying to do something using the lookup function but I still can´t make it work, I´m using this:

Table1:

LOAD A,

     Name,

     ID

FROM

(ooxml, embedded labels, table is Hoja1);

Concatenate(Table1)

LOAD A,

     Name,

     ID

FROM

(ooxml, embedded labels, table is Hoja1);

Table3:

LOAD Name    as Name3,

     ID        as ID3

FROM

(ooxml, embedded labels, table is Hoja1);

T4:

Load *

    ,IF(Name='',LOOKUP(Name3,ID3,ID,'Table3'),Name) as test

Resident Table1;

Drop Table Table1;

[swuehl]

Hi Camilo,

please look in the script of the example I attached. Tableview is CTRL-T and the right click on table and preview.

Regards,

Stefan

P.S: Here is my script excerpt:

Table3:

LOAD Name as NameTemp,

     ID as IDTemp

FROM

[http://community.qlik.com/thread/32134?tstart=0]

(html, codepage is 1252, embedded labels, table is @3, filters(

Remove(Row, Pos(Top, 1))

));

Final:

LOAD A,

     Name,

     lookup('IDTemp','NameTemp',Name,'Table3') as ID

FROM

[http://community.qlik.com/thread/32134?tstart=0]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

LOAD A,

      lookup('NameTemp','IDTemp',ID,'Table3') as Name,

     ID

FROM

[http://community.qlik.com/thread/32134?tstart=0]

(html, codepage is 1252, embedded labels, table is @2, filters(

Remove(Row, Pos(Top, 1))

));

drop table Table3;

[swuehl]

Hi,

with your code, please note that lookup takes string literals as first, second, fourth parameter.

Stefan