Outer Join with Null's in join field

[Anonymous]

Hi

I need to Outer Join 2 tables that have a single common field to join on.

Many of the values in this join field are Null, but I wish still wish to join on the Null's.

One thing I thought was replacing the Null's in each table with say the text 'FieldIsNull' and then after the join doing a resident load to set these fields back to Null.

Any suggestions on the best way to do this would be welcome.

Many Thanks,     Bill

[Gysbert_Wassenaar]

One thing I thought was replacing the Null's in each table with say the text 'FieldIsNull'

That's what I'd do, perhaps using another string.

and then after the join doing a resident load to set these fields back to Null.

And that's what I would not do.

[Anonymous]

Unfortunately the dashboard needs to the resulting table to have the values reset back to Null.

[Gysbert_Wassenaar]

I doubt that. What's the real requirement? What is the question that "Show Null Values" is your answer to?

[Peter_Cammaert · Accepted solution]

What about this then?

:

NullAsValue JoinFieldName;

OUTER JOIN (...)     // Will also link/join on NULL values

LOAD...;

NullAsNull JoinFieldName;

:

No additional Resident LOADs needed. Might save you a few seconds...

[Anonymous]

I am adding an additional dimension, called say [NewDim] to an existing table in an existing qvw.

When this [NewDIm] is added to existing Chart Objects and Suppress Null Values ticked it should not display the Nulls. 

I will not succeed persuading people to add Set Analysis to not display where the text value of 'FieldIsNull'.

[Anonymous]

Peter,  Sounds promising I'll test it now and advise what happens.

[Anonymous]

Peter,

Brilliant & many thanks.

Although I did need to put the first NullAsValue JoinFieldName; before the load of the table before that it is Outer Join 'ing to.

Thanks Again,    Bill

[Peter_Cammaert]

Indeed, Bill, I discovered this small requirement immediately after posting and when doing a small test using INLINE tables.

It must be like that because NullAsValue is like a field attribute, and not some quality of an arbitrary column. Also you cannot apply NullAsValue retroactively (it's more like a switch, a bit like the QUALIFY statement), and there you have it. Mixing field values from before and after NullAsValue will never produce the exepected result when JOINing tables.

In any case, many thanks for your feedback and confirmation.

[Anonymous]

... and to end up with true Null 's in the final table it needs a little jiggery pokery :

NullAsValue [NewDim];

          Load FirstTable ......................

          Load FinalTable ......................

NullAsNull [NewDim];

OUTER JOIN(FinalTable)

LOAD

  if ( len ( [NewDim] ) > 0  , [NewDim] , null() ) as [Temp NewDim] ,

  *

Resident FirstTable

;

drop table FirstTable ;

drop field [NewDim] ;

rename field [Temp NewDim] to [NewDim] ;

Many thanks again for the prod in the right direction.