topic Re: IsNull function not working in script in QlikView

IsNull function not working in script

Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I am having a Issue with Isnulll function. Some of expert can please help me to resolve this Issue

I am having field called Date.I want to show 0 when there is no Date got selected.

In front end side I am using formula in Text Object if(Isnull(Dates) = -1,0,1) we expected output 0. It's getting as expected.

I have written Same formula in script

Test:

LOAD

Dates,

if(IsNull(Dates)=-1,0,1) as [Date Info]

FROM

xyz.qvw

but [Date Info] is displaying only 1. Not even 0. Just like in below Screen shot

Some can plz help on this. I am attaching sample data and sample qvw

Thank you,

Swarupaaa

Re: IsNull function not working in script

sunny_talwar — Wed, 10 Aug 2016 15:14:54 GMT

In the attached sample, there are no places where date is null. Have you tried adding a row where date is actually null?

Re: IsNull function not working in script

kruppas78 — Wed, 10 Aug 2016 15:15:44 GMT

Hi,

i mean the correct code will be:

if(IsNull(Dates),0,1) as [Date Info]

Re: IsNull function not working in script

marcus_sommer — Wed, 10 Aug 2016 15:16:01 GMT

Try it with: if(len(trim(Dates))=0,0,1) as [Date Info]

- Marcus

Re: IsNull function not working in script

santiago_respane — Wed, 10 Aug 2016 15:16:43 GMT

Hi,

if you are trying to display 1 if a value is selected in the Dates field and 0 if there's no value selected you can add a text object with the following formula:

=IF(GetSelectedCount("Dates")>0,1,0)

You don't need to apply that logic in the script.

Please let me know ir this helps.

Kind regards,

Re: IsNull function not working in script

sunny_talwar — Wed, 10 Aug 2016 15:17:20 GMT

When I added few nulls in your attached Excel file

I see 0

For your actual application, you might have white spaces and that's why IsNull isn't working. Try with Len(Trim()) function

If(Len(Trim(Dates)) > 0, 1, 0) as [Date Info]

Re: IsNull function not working in script

Wed, 10 Aug 2016 15:20:25 GMT

Hi Sunny,

Is it possible by using len function?. why because my requirement is

if(Dates = "",0,1) so I thought it can be done by using Isnull. Is there any alternate method

Thank you,

Swarupaa

Re: IsNull function not working in script

Wed, 10 Aug 2016 15:23:08 GMT

Hi Marcus,

Thanks for Reply. I have tried this but it's not working

Re: IsNull function not working in script

Wed, 10 Aug 2016 15:28:29 GMT

Hi Sunny,

Thank you so much for Answer. But in my data there is null values. only if we select only it have to show 1.

Re: IsNull function not working in script

beck_bakytbek — Wed, 10 Aug 2016 15:31:19 GMT

Hi Swarup,

check this : Show date Dates with 0 value if data not available

Re: IsNull function not working in script

Wed, 10 Aug 2016 15:33:02 GMT

Hi Stefan

Thank you for your reply. I have Tried this but it's not working

Re: IsNull function not working in script

kruppas78 — Wed, 10 Aug 2016 15:46:02 GMT

Problem is that you only read 1 column.

on which date should QlikView set the value 0?

You can use

LOAD RowNo()+1 as Row,

Dates,

if(IsNull(Dates),0,1) as [Date Info]

the Row will Show you the Excel file Row when there is an empty date.

Re: IsNull function not working in script

Wed, 10 Aug 2016 16:40:20 GMT

Thank you, Stefan