Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Outer Join with Null's in join field

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

1 Solution

Accepted Solutions

Re: Outer Join with Null's in join field

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...

9 Replies

Re: Outer Join with Null's in join field

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.


talk is cheap, supply exceeds demand

Re: Outer Join with Null's in join field

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

Re: Outer Join with Null's in join field

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


talk is cheap, supply exceeds demand

Re: Outer Join with Null's in join field

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...

Re: Outer Join with Null's in join field

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'.

Re: Outer Join with Null's in join field

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

Re: Outer Join with Null's in join field

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

Re: Outer Join with Null's in join field

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.

Re: Outer Join with Null's in join field

... 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.

Community Browser