Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

IsNull Function

The chart that I have generated is displayed as follows. This is as a result of association where there was no value for this field against the customer number originating from another source.

I understand that if I apply IsNull function to it, these records should return true.

Example. I have derived a new field in chart with expression:

If(IsNull[Reporting Date], 'Yes,'No')       //* Ignore the field name in expression to chart shown *//

When I apply this, I get 'No' correctly wherever Reporting Date had valid values. But I am not getting a 'Yes' where Reporting Date was displaying as ' - '.  Instead I get the same value '-' displayed in derived field.  Could you please help me with this or explain why is this happening?

11 Replies
MK_QSL
Not applicable

Re: IsNull Function

May be those fields are not associated with your dimension.

Check below blog post.

Generating Missing Data In QlikView

surendraj
Not applicable

Re: IsNull Function

May be this

if(isnull([Reporting Date]) OR [Reporting Date]= '','Yes','No')

or may be this

if(isnull([Reporting Date]) OR [Reporting Date]= '-','Yes','No')

Not applicable

Re: IsNull Function

You are correct. It is actually Missing Data. I have associated two tables with Customer ID as the key. I need to know what all Customer IDs of Table 1 had a match with Table 2. I took one of the fields from Table 2 which is Reporting Date and evaluated for NULL. For Customers with a match, the Reporting Date will have a value. For Customers without a mTach The reporting Date displays as '-'. Can this be evaluated to IsNULL?

bellamae
Not applicable

Re: IsNull Function

Can you check that you have a closed quote on the yes in the actual expression as the one above is missing it.

If(IsNull[Reporting Date], 'Yes','No') 

not

If(IsNull[Reporting Date], 'Yes,'No') 

yasmeenk
Not applicable

Re: IsNull Function

Hi,

Can you try like this?

If(Len(Trim(Reporting Date))>0,'No','Yes')

praveenprithivi
Not applicable

Re: IsNull Function

I think above one is best practice

Lech_Miszkiewicz
Not applicable

Re: IsNull Function

Hi,

It can be evaluated if you join tables together in script (that will actually create null values in the table) and then use - also in script - If(IsNull[Reporting Date], 'Yes,'No') to fill null values and create flag Y/N.

If data is not associated then it will not work on frontend unless you create missing keys.

regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Lech_Miszkiewicz
Not applicable

Re: IsNull Function

BTW. You are using autocalendar from what you attached as a screenshot. I am not sure how this will work and i would suggest using dedicated field from one of your 2 tables. I quess your autogenerated model has at least one more table there.

regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Not applicable

Re: IsNull Function

I think this may help you..


If(IsNull([Reporting Date])=-1,'No','Yes')