Concatenate

[#1]

Trying to concatenate data from two tables.

Table1_ID	Table1_Letter
1	a
2	b
3	c

Table2_ID	Table2_Letter	Table2_Dum
4	d	aa
5	e	bb
6	f	cc
1	dd	DD

The result Im searching for is as follows

Table1_ID	Table1_Letter
1	a
2	b
3	c
4	d
5	e
6	f

This is the script I'm using.  I've tried so many different versions and just cant seem to get it right.  Appreciate your help.

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

     Table2_Letter as Table1_Letter,

     Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

Concatenate(Table1)

LOAD Table1_ID,

     Table1_Letter

//     Table2_Dum

Resident Table2Temp

where NOT EXISTS (Table1_ID);

[Anonymous]

Why not do a Left join (Table1)?

That way you'll only keep the ID's that exist in table 1 if they are present in both tables.

[#3]

This is what I now have with the Left Join - still producing the same results.

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

     Table2_Letter as Table1_Letter,

     Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

TableFinal:

NoConcatenate

LOAD *

Resident Table1;

Left Join

LOAD Table1_ID,

     Table1_Letter

resident Table2Temp

where NOT EXISTS (Table1_ID);

[giakoum]

In your example, record

1

dd

DD

must not be in the result set. What is the logic here? You include

1

a

because it was first encuntered or... ?

[#5]

Correct

1          dd          DD

must not be included.

The logic is that the data in Table1 must all be included plus the data in Table2 where the Table2_ID doesnt exist in Table1.

[giakoum]

Please see attached

[#7]

Thanks Ioannis - that worked for this example.

I now need to apply this to much larger tables with 30 columns each and each with different names.

Am I able to do something like this?

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

LOad Table2_ID as Table1_ID,

           Table2_Letter as Table1_Letter

where not exists (Table2_ID);

LOAD Table2_ID,

     Table2_Letter

FROM

(ooxml, embedded labels, table is Sheet2);

[giakoum]

If you have one key column (ID) then the logic is pretty much the same. But some sample data would help, if available.

[nagaiank]

I think your where cause should be as follows:

where not exists (Table1_ID,Table2_ID)

[Anonymous]

What I meant was, why not do like this?

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Left join (Table1)

LOAD Table2_ID as Table1_ID,

     Table2_Letter as Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet2);