topic Re: Problem Trying to Filter Null values in QLIK Sense in App Development

Problem Trying to Filter Null values in QLIK Sense

Anonymous — Fri, 20 Jul 2018 01:09:30 GMT

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

Re: Problem Trying to Filter Null values in QLIK Sense

Chanty4u — Fri, 20 Jul 2018 16:52:07 GMT

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

it will work.

Re: Problem Trying to Filter Null values in QLIK Sense

PabloTrevisan — Fri, 20 Jul 2018 17:14:58 GMT

Hi,

You could not make the condition via script?

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

Re: Problem Trying to Filter Null values in QLIK Sense

dwforest — Fri, 20 Jul 2018 20:37:34 GMT

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

Re: Problem Trying to Filter Null values in QLIK Sense

BalaBhaskar_Qlik — Sat, 21 Jul 2018 11:13:04 GMT

May be these:

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

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

Re: Problem Trying to Filter Null values in QLIK Sense

Anonymous — Sun, 22 Jul 2018 22:27:55 GMT

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.

Re: Problem Trying to Filter Null values in QLIK Sense

Anonymous — Sun, 22 Jul 2018 22:29:42 GMT

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.

Re: Problem Trying to Filter Null values in QLIK Sense

Anonymous — Sun, 22 Jul 2018 22:34:03 GMT

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.

Re: Problem Trying to Filter Null values in QLIK Sense

Anonymous — Sun, 22 Jul 2018 22:48:30 GMT

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.

Re: Problem Trying to Filter Null values in QLIK Sense

Anonymous — Mon, 23 Jul 2018 04:17:53 GMT

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

Re: Problem Trying to Filter Null values in QLIK Sense

skychris — Fri, 21 Jun 2019 04:15:37 GMT

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