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

[Chanty4u]

try to create as  master dimension and use that in chart or table

it will work.

[PabloTrevisan]

Hi,

You could not make the condition via script?

if(Not isnull(len(trim([Lease Ref]))),'Leased','Not Lease') as [Leased Units]

[dwforest]

the field is null not empty, so len() will return null not 0.  Use IsNull() to test for null.

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/NULLFunctions/IsNull.htm

[balabhaskarqlik]

May be these:

Aggr(if(Len(trim([Lease Ref]))>0,Count([Lease Ref])),[Leased Units])

Count({<[Lease Ref] = {'*'}>}Aggr(Count([Lease Ref]),[Leased Units]))

[Anonymous]

Sorry, I did not make myself clear. I did create a Master Dimension (=if(len(trim([Lease Ref]))=0,'No Lease','Leased')) and used it in the table as Leased Units. It shows when they are leased but just - when not.

Dragging the Master dimension onto the Desktop only shows the Leased choice.

[Anonymous]

I regret that I'm not sure what you mean by "via script". In the load script? Or a Master Dimension? Used to Tableau so find some things quite different in Qlik Sense.

[Anonymous]

That makes a lot of sense but actually made no difference at all. I changed my Master Dimension to =if(ISNULL([Lease Ref]),'No Lease','Leased'). Pity. Thought it would work.

[Anonymous]

Hi. Thanks for the reply but I'm not sure how I would use your suggestions. Create Master dimensions?

What I want to do is filter my table so I just see all the Units that just have - as the Lease Ref, as in Unit Ref 0.07 below. My Master dimension Leased Units (=if(len(trim([Lease Ref]))=0,'No Lease','Leased')) only allows me to just see all those that are Leased or both (Leased and - as below). Changing it to =if(ISNULL([Lease Ref]),'No Lease','Leased') makes no difference.

[Anonymous · Accepted solution]

Solved.

After trying various Master Dimension expressions, including the two below which should work:

    =If(NOT IsNull([Lease Ref]) OR Len(trim([Lease Ref]))<>0,'Leased','Not Leased')

    =If([Lease Ref] & '?' = '?','Not Leased','Leased')

I eventually tried:

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

This works! Haven't got the faintest clue why it does when the 2nd one didn't but, who cares.

Thank you all for your help.

Regards,

Peter