Problem Trying to Filter Null values in QLIK Sense

[Anonymous]

Hi,

I have the following table:

The Unit Ref comes from one table which does not have the Lease Ref and the Lease Ref from another that does not have the Unit ref. They are linked via a 3rd Table which has both. Hope that makes sense.

I want to be able to filter so that we can just see all the units that don't have a Lease ref in the table, though to be able to filter either way would be preferable (just those with lease refs and then just those without).

I have created a dimension called Leased Units (3rd column in  the table above) that only works for Units that have a Lease ref, and it is driving me nuts trying to figure out how to get the 'No Lease' part to work.

The dimension is =if(len(trim([Lease Ref]))=0,'No Lease','Leased')

Dragging that onto the dashboard only gives me one choice:

Selecting "Leased" in the filter I only see all the records that do have a lease ref.

I'm fairly new to QLIK Sense but I'm sure I will kick myself when I find out how to do this.

Any help greatly appreciated.

Thanks,

Peter

[skychris]

Hi Peter,

I got the same scenario as you,  no matter what statement I use,  len(trim),  purchar etc,   the dimension value as display as '-',  want to understand your workable solution

 =If([Lease Ref] & [UNIT.Unit Ref]=[UNIT.Unit Ref],'Not Leased','Leased')

what is this UNIT.Unit Ref ?

Other guru :-   basically I got a table MEMBER with UID, member in the data load editor,    the other table got UID, Name.................. etc

in the QS Hub,  the table with member dimension show '-' for those not appearing in the  MEMBER table,   and those appearing in the table will be 'Y',   wanted to create an express for those '-' to 'Non Member',  no matter how I do it still '-'

Please help