topic Re: Table Join - create constant instead of null where join field not present in QlikView

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

mazacini — Thu, 17 Sep 2015 12:22:07 GMT

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

Table B

FieldA FieldB

1 A

2 B

3 C

After Join

TableA

FieldA FieldB

1 A

2 B

3 C

4 None

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

Thu, 17 Sep 2015 12:37:13 GMT

Use this script in load:

TableA:

Load * Inline [

FieldA

];

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;

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

maxgro — Thu, 17 Sep 2015 12:40:58 GMT

TableA:

load * inline [

FieldA

];

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;

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

Thu, 17 Sep 2015 12:46:42 GMT

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);

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

mazacini — Thu, 17 Sep 2015 20:49:48 GMT

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