topic ISNULL function part 2 in QlikView

ISNULL function part 2

Wed, 29 Apr 2009 19:27:59 GMT

Hello Fellow users,

I have the following challenge. I have 3 fields BillCode, BillCodeNew and Result. I would like to create the following calculation:

If (BillCodeNew = Blank or has the Same Value as BillCode, then field Result should have the value of BillCode, else the field Result should have the value of BillCodeNew)

I tried to solve this with the following script:

=if(BillCodeNew =' ' or BillCodeNew = BillCode, BillCode,BillCodeNew) as Result

Greetings from Holland,

Aissam

ISNULL function part 2

Wed, 29 Apr 2009 19:30:49 GMT

Try this:

=If(IsNull(BillCodeNew),BillCode,BilCodeNew) AS Result

I don't know if it is necessary to include the prefix "JC."

Good luck!

ISNULL function part 2

Wed, 29 Apr 2009 19:45:25 GMT

Aissam,

Its really important to differentiate between 'blank', 'space' and null. A field may actually have a value of space and may be represented by ' ' (a space between single quotes) whereas null may be represented by '' (single quotes with nothing between. A space actually has a value of hex20 in ASCII and so is as much a character as 'a' or any other. Null on the otherhand is undefined - nothing at all.

Regards,

Gordon

ISNULL function part 2

Wed, 29 Apr 2009 20:03:45 GMT

I will try to give more information. I have two tables the first is

JC:

Load BillCode, job

from Excel

the second one is (in this table I only have jobs that should have the BillCodeNew)

Reallocation:

load BillCodeNew,job

From Excel1

The am doing the calculation in a pivot table.

Regards,

Aissam

ISNULL function part 2

Anonymous — Wed, 29 Apr 2009 21:03:52 GMT

Another thing to keep in mind regarding null values and different type of null values:

Due to optimizations present in X64 versions of QV using the ISNULL() function might give different results when compared to the 32bit version of QV. Without going in to too much detail on the technical side of things it's recommended to use Len(FIELDNAME)=0 instead of isnull() to get consistant results across platforms.

X64 is more optimized than 32-bit when it comes to performance which also means that the tools for handling null value logics are simplified in comparison to the older 32-bit platform. To put it simply, there are situations where a x64 machine can't tell the difference between a Null value and a "missing" value, that is a null value which is not a result of data input but is created when data fields are joined together in a relation that is not 1-1.

These differences can't be caught using the IsNull() functionality under x64 even though it works on 32-bit where the testing can be performed on several levels. With x64 you are stuck with a simpler form of Null testing that only finds the explicit Null values.

By using if(len(<FieldName>)=0,..) you get the same functionality for x64 that IsNull() delivers on 32-bit with empty strings, missing values and data driven null values

ISNULL function part 2

Thu, 30 Apr 2009 02:12:51 GMT

Well that explains a few things!! Although, it doesn't feel right to be treating my QlikView data as if it came out of DB2!

ISNULL function part 2

Oleg_Troyansky — Thu, 30 Apr 2009 02:40:51 GMT

Aissam,

1. I use len(trim(BillCodeNew)) = 0 as a condition that covers all possible cases of "space" or null() or missing.

2. The second condition (BillCode=BillCodeNew) is redundant - if the two codes are the same, it doesn't matter which one do you use.

So, the final formula should be:

if(len(trim(BillCodeNew)) > 0, BillCodeNew, BillCode)

regards,

Oleg

ISNULL function part 2

Sat, 30 May 2009 07:08:22 GMT

Thanks jsn. you made my friday night a happy night.