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

[#1]

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

[sasiparupudi1]

sorry, I think it should be

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

[dusasuman]

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..

[daniel_kusiak]

Hi AdamHayden,

Maybe this will work for you:

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

[sasiparupudi1]

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')))

[jonathandienst]

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

See Preparing examples for Upload - Reduction and Data Scrambling

[#7]

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