Join two tables and fill up null values from both tables

[#1]

I have two Tables below. They have the same key and field. The reason to merge the two tables is that I want to fill up the null values in TableA with values from TableB but still keep all key in from both tables.

TableA:

Key	Field
1	10
2
3	30

TableB:

Key	Field
2	20
4	40

The merged table should be look like:

Key	Field
1	10
2	20
3	30
4	40

Thank you in advance!

[sushil353]

Hi,

Try something like this:

LOAD *,len(Field) Inline
[
Key,Field
1,10
2
3,30
]
where  len(Field)>0;

Concatenate
TableB:
load*,len(Field)
Inline
[
Key,Field
2,20
4,40
]
;

HTH

Sushil

[effinty2112 · Accepted solution]

Hi Yuan,

Try:

Table1:

LOAD * INLINE [

    Key, Field1

    1, 10

    2,

    3, 30

];

Outer Join(Table1)

LOAD * INLINE [

    Key, Field2

    2, 20

    4, 40

];

Table:

LOAD

Key,

Alt(Field1,Field2) as Field

Resident Table1;

Drop Table Table1;

Regards

Andrew

[#4]

Thanks for your help. Sushil

What if there is no value for the additional Key = 5 in TableA, there are still no values from TableB? How could I keep key =5 with null value?