Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

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

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

15 Replies
Employee
Employee

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

Try

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

daniel_kusiak
Contributor II

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

Hi Adam Hayden,

In script editor try:

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

Not applicable

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

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

MVP
MVP

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
dusasuman
Contributor

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

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

Not applicable

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

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

dusasuman
Contributor

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

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

Digvijay_Singh
Honored Contributor III

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

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

See the attached sample how you are trying differently..

sasiparupudi1
Honored Contributor III

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

if(Len(Field1)>0,Field1,

    if(Len(Field2)>0,Field2,

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