Table Join - create constant instead of null where join field not present

[mazacini]

Hi

I have FieldA in TableA.

I have FieldA, FieldB in TableB.

I want to join TableB to TableA, but create a constant 'None' for FieldB for those FieldA values not in TableB.

Any ideas?

Thanks

So in Table A

FieldA

1

2

3

4

Table B

FieldA     FieldB

1               A

2               B

3               C

After Join

TableA

FieldA     FieldB

1               A

2               B

3               C

4               None

[#2 · Accepted solution]

Use this script in load:

TableA:

Load * Inline [

FieldA

1

2

3

4

];

Left Join(TableA)

Load * Inline [

FieldA,FieldB

1,A

2,B

3,C

];

NoConcatenate

TableFinal:

Load FieldA,

     if(isNull( FieldB ),'None',FieldB) as FieldB

Resident TableA;

Drop Table TableA;

[maxgro]

TableA:

load * inline [

FieldA

1

2

3

4

];

left join (TableA)

load * inline [

FieldA,     FieldB

1      ,         A

2       ,        B

3        ,       C

];

Final:

NoConcatenate load

  FieldA,

  if(IsNull(FieldB), 'None', FieldB) as FieldB

Resident TableA;

DROP Table TableA;

[#4]

Hi,

you can simply use a mapping table for that purpose. The code should look like:

TableB:
Mapping LOAD
    FieldA,
    FieldB
FROM
[yourqvd.qvd]
(qvd);

TableA:
LOAD
    FieldA,
    ApplyMap('TableB', FieldA, 'None') as FieldB;
FROM
[yourotherqvd.qvd]
(qvd);

[mazacini]

Thank you Claus.

For some reason, i have been avoiding use of the mapping functionality.

You solution has actually indicated how useful it can be (and easy - when you know how)!

Thanks for your help

Joe