topic Re: Outer Join with Null's in join field in QlikView

Outer Join with Null's in join field

Anonymous — Tue, 17 Mar 2015 13:13:17 GMT

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

Re: Outer Join with Null's in join field

Gysbert_Wassenaar — Tue, 17 Mar 2015 13:20:07 GMT

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.

Re: Outer Join with Null's in join field

Anonymous — Tue, 17 Mar 2015 13:32:55 GMT

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

Gysbert_Wassenaar — Tue, 17 Mar 2015 13:36:44 GMT

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

Re: Outer Join with Null's in join field

Peter_Cammaert — Tue, 17 Mar 2015 13:41:24 GMT

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

Anonymous — Tue, 17 Mar 2015 13:50:22 GMT

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

Anonymous — Tue, 17 Mar 2015 13:54:41 GMT

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

Re: Outer Join with Null's in join field

Anonymous — Tue, 17 Mar 2015 14:20:59 GMT

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

Peter_Cammaert — Tue, 17 Mar 2015 14:37:15 GMT

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

Anonymous — Tue, 17 Mar 2015 15:50:46 GMT

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