Null values in tables

[saviostrazzullo]

I linked 2 tables A and B and I need to select rows in A which are not present in B. I can't select  Null values in A and I tryed to insert a function in the field of the object: if (IsNull (field), 'T','F') but it doesn't recognize the Null Value. I also tryed to set null value in the script but it doesn't work.

Thank you

Savio

[#2]

Try doing during the load script:

LOAD

...

if(isNull(field),0,1) as field

...

FROM

...

Here is how QlikView handles Null values: NULL handling in QlikView

[jonathandienst]

Load a source field and count field for each table like this:

TableA:

LOAD ...

  LinkID,

  ...

  'TableA' As Source,

  1 As Counter

FROM ...

TableB:

LOAD ...

  LinkID,

  ...

  'TableB' As Source,

  1 As Counter

FROM ...

Then in a straight table with

Dimension

  LinkID

Expressions

  Sum({<Source = {'TableA'}>} Counter)

  Sum({<Source = {'TableA'}>} Counter)

or

  Sum({<Source = {'TableA'}>} Counter)

  If(Sum({<Source = {'TableA'}>} Counter) > 0 And   Sum({<Source = {'TableA'}>} Counter) = 0, 'Missing from TableB')

HTH

Jonathan

[#4]

I would add it initially with loading from your source data, so with a SQL source for example just script is as isnull(field,value). In value you can assign it a zero value or whichever value you want it to be

[ToniKautto]

I am not sure I fully understand the issues you are having. Could you attach a sample file to show your script and data?

For NULL values in general, keep in mind that they represent nothing. In other words NULL is the representation when you have a undefined or missing value. It is not possible to select NULL, as you can not select a value that does not exist.

Being a missing value NULL also has the string length of zero. A empty string can also sometimes be mistaken for NULL, as they also have no visible value. Try using Len() to see what length your values have.

=If( Len(field)=0, 'T','F')

[saviostrazzullo]

I tryed to insert the statement in the table A and in the Table but I have null values only after the join. of A with B. I linked them using a same name for LinkId. Maybe when it loads data it didn't find null values and only when I try to show them I can see them. I also tryed to insert the statement in the expression field of the table but it als doesn't work. It doesn't recognize null value.

[saviostrazzullo]

This is a gook workaround but I can't select the value 'Missing '. In your solution I can see the value 'Missing' but I can't select them.

[saviostrazzullo]

I tryed to insert the statement in the table A and in the Table but I have null values only after the join. of A with B. I linked them using a same name for LinkId. Maybe when it loads data it didn't find null values and only when I try to show them I can see them. I also tryed to insert the statement in the expression field of the table but it als doesn't work. It doesn't recognize null value.

[saviostrazzullo]

Toni I have 2 tables A and B:

Table A
Load
LinkId
...
FieldA

Table B
Load
LinkId
...
FieldB

I need to search rows in A that are not present in B.

If I use a table view :

LindId, FiledA FieldB

I have null values in FieldB that I can't select, I would find and select these rows in A that are not present in B.

I tryed you solution but the table gave me null values also as a result of len expression.

[#10]

Hi Savio,

To deal with Nulls I use the below in the variable setting portion of my script. this will set any null value to be displayed as "NULL." You will then be able to select null values as a chart selection, and use in functions.

NullAsValue *;

Set NullValue = '<NULL>';