topic Re: Sometimes doesn't recognize Null values, but does others. in QlikView

Sometimes doesn't recognize Null values, but does others.

Thu, 06 Aug 2015 16:23:00 GMT

I have two fields that contain a mixture of data and null values each, and what I am trying to do is replace the Nulls in each field with 'No Fieldname.' and then join both the fields together with an ' - ' in the middle.

I decided to do this using the following formula:-

=If(IsNull(Field1), 'No Field1', Field1) & ' - ' & if(IsNull(Field2), 'No Field2', Field2)

but looking at the field afterwords, i just get a blank space where any of the Null positions were from Field1 or 2.

More confusingly, If do either of the tests on their own, they work, IE

=If(IsNull(Field1), 'No Field1', Field1)

produces the correct results for field1 with nulls as 'No Field 1'

=if(IsNull(Field2), 'No Field2', Field2)

produces the correct results for field2 with nulls as 'No Field 2'

its only when I try to do them together suddenly it returns nothing for the nulls.

I'm really scratching my head about this one, any help anyone would offer would be greatly appreciated.

Many thanks,

Adam Hayden

Re: Sometimes doesn't recognize Null values, but does others.

Clever_Anjos — Thu, 06 Aug 2015 19:42:41 GMT

Try

=alt(Field1, 'No Field1') & ' - ' & alt(Field2, 'No Field2')

Re: Sometimes doesn't recognize Null values, but does others.

daniel_kusiak — Thu, 06 Aug 2015 19:45:33 GMT

Hi Adam Hayden,

In script editor try:

If(IsNull(Field1), 'No Field1', Field1) &'-'& If(IsNull(Field2), 'No Field1', Field2) as Field3

Re: Sometimes doesn't recognize Null values, but does others.

Fri, 07 Aug 2015 08:38:37 GMT

Thank you both for your replies,

I first tried using "=alt(Field1, 'No Field1') & ' - ' & alt(Field2, 'No Field2')" but this resulted in every cell from Field1 and 2 that had data, displaying as "'No field1' - 'No field2'" and any field from field 1 or 2 that was blank, resulted in the formula returning a blank as well.

I then tried adding If(IsNull(Field1), 'No Field1', Field1) &'-'& If(IsNull(Field2), 'No Field1', Field2) as Field3 to the script editor. This produced the correct result when Field 1 and 2 contained data, but when they were empty the resultant Field 3 was also empty.

Its almost as if the program isn't seeing the fields as Null and is copying the 'Blank'. but paradoxically, if i make a field 4 with =If(IsNull(Field1), 'No Field1', Field1) for a row where field 1 is blank this field returns 'No Field1'!

Any further suggestions are much appreciated.

Many thanks,

Adam Hayden

Re: Sometimes doesn't recognize Null values, but does others.

jonathandienst — Fri, 07 Aug 2015 08:49:22 GMT

IsNull() will detect null values, and I am not sure whether you have null values or blank values - blank values are not null, so IsNull will return false. To detect blanks and nulls:

=If(Len(Field1) = 0, 'No Field1', Field1) & ' - ' & if(Len(Field2) = 0, 'No Field2', Field2)

A blank string and null both have a length of 0.

Re: Sometimes doesn't recognize Null values, but does others.

dusasuman — Fri, 07 Aug 2015 09:14:15 GMT

Hi Adam Hayden,

This code may resolves the issue...

If(Len(Trim(Field1))>0, Field1,'No Field') &'-'& If(Len(Trim(Field2))>0, Field2,'No Field') as Feld3

If not try to add the logic of ISNULL() to above logic. The combination of Finding the length for having empty values and null values.

--Suman

Re: Sometimes doesn't recognize Null values, but does others.

Fri, 07 Aug 2015 09:32:59 GMT

Thank you again for more suggestions,

I tried using If(IsNull(Field1) or Len(Trim(Field1))<1, 'No Field1', Field1) &'-'& If(IsNull(Field2) or Len(Trim(Field2))<1, 'No Field2', Field2) as FIeld3

This once again displays data correctly for all the times Field1 and 2 have data, but all the times either is blank it is just returning a blank in field 3.

Its strange that if I use =If(IsNull(Field1), 'No Field1', Field1) it correctly recognizes Nulls in Field 1, but when I ask it to do that with two fields, it suddenly stops seeing either.

your continued help is much appreciated.

Many thanks

Adam Hayden

Re: Sometimes doesn't recognize Null values, but does others.

dusasuman — Fri, 07 Aug 2015 09:52:30 GMT

that was really strange. The has to show the values with

Len(Trim(Field1))<1, 'No Field1', Field1) &'-'& If(IsNull(Field2) or Len(Trim(Field2))<1, 'No Field2', Field2) as FIeld3 .

Not sure how its happening

Provide sample data code if possible

--Suman

Re: Sometimes doesn't recognize Null values, but does others.

Digvijay_Singh — Fri, 07 Aug 2015 10:24:30 GMT

The length check is considered best option in such situations as mentioned by jontydkpi

See the attached sample how you are trying differently..

Re: Sometimes doesn't recognize Null values, but does others.

sasiparupudi1 — Fri, 07 Aug 2015 11:33:16 GMT

if(Len(Field1)>0,Field1,

if(Len(Field2)>0,Field2,

if(Len(Field1) = 0 and Len(Field2) = 0, 'No Field1 - No Field2')))

Re: Sometimes doesn't recognize Null values, but does others.

sasiparupudi1 — Fri, 07 Aug 2015 11:36:47 GMT

sorry, I think it should be

if(Len(Field1) = 0 and Len(Field2) = 0, 'No Field1 - No Field2',Field1 &'-'& Field2)

Re: Sometimes doesn't recognize Null values, but does others.

dusasuman — Fri, 07 Aug 2015 11:49:10 GMT

That works only when the both the fields having the null or 0 length values. It excludes other possible combination of Field1 having a null and Field2 having a data. and vise versa..

Re: Sometimes doesn't recognize Null values, but does others.

daniel_kusiak — Fri, 07 Aug 2015 11:57:10 GMT

Hi AdamHayden,

Maybe this will work for you:

if(Field1='','NoField1',Field1) &'-'& if(Field2='','NoField2',Field2) as Fieldxxx

Re: Sometimes doesn't recognize Null values, but does others.

sasiparupudi1 — Fri, 07 Aug 2015 12:06:28 GMT

like this?

if(Len(Field1)>0 and Len(Field2)=0,Field1 &'- No Field2',

if(Len(Field1)=0 and Len(Field2)>0,Field2 &'- No Field1',

if(Len(Field1) = 0 and Len(Field2) = 0, 'No Field1 - No Field2')))

Re: Sometimes doesn't recognize Null values, but does others.

jonathandienst — Fri, 07 Aug 2015 12:12:27 GMT

Perhaps you should post your application, or at least a sample the illustrates the problem.

See Preparing examples for Upload - Reduction and Data Scrambling

Re: Sometimes doesn't recognize Null values, but does others.

Fri, 07 Aug 2015 13:25:51 GMT

Thanks for more suggestions,

I tried using the =" but then I tried that, it colored out the rest of the equation, only doing ="" seemed to work and again it game me exactly the same results as before.

I have submitted a ticket to QlikView support to see if they can shed any light on this, as whats happening doesn't seem to make logical sense.

Thank you everyone for your suggestions and ideas, I greatly appreciate the help you have been.

Many thanks,

Adam Hayden