NULL in TI

[robchr]

I'm trying to get a null value to go into an element called 'NA', but I need to check for nulls properly.

Currently, I am using something like:

IF(ELEMENT@='');
ELEMENT='NA';
ENDIF;

is using '' the proper way to check for nulls coming from the data source? Even though blank string is not the same as null.

Thanks!

[Alan Kirk]

I'm trying to get a null value to go into an element called 'NA', but I need to check for nulls properly.

Currently, I am using something like:

IF(ELEMENT@='');
ELEMENT='NA';
ENDIF;

is using '' the proper way to check for nulls coming from the data source? Even though blank string is not the same as null.

Nulls can be a pain to work with at the best of times. However the concept doesn't really exist in TM1 if you ignore Undefvalues (which are also a pain to work with). As far as TI is concerned there are no such things as Nulls, merely String variable types and Numeric variable types.

Doing a quick and dirty check with Access (best to confirm that this is the case with your data source as well), if a field contains a null value and it's defined as a String variable type, then yes, as far as TI is concerned it's a zero length string and your test will work.

If defined as a Nueric variable type, a null value will render as a 0, and that's what you'd test for.

[paulsimon]

Rob

I would recommend that you handle the Nulls in the SQL statement that reads from your data source.

In SQL Server you can say ISNULL(ELEMENT,'NA') as ELEMENT which means that if the ELEMENT column is Null that it will be replaced by 'NA' otherwise you will get whatever value is in ELEMENT. The as ELEMENT bit just gives the result an Alias so that TM1 has something that it can derive a variable from.

You will need to find the particular syntax for your source database.

Regards


Paul Simon

[robchr]

Thank you both for the insight. I was trying to avoid using SQL statements against the data source because it is already a union query (and already crunching a lot of values). But I think it best to try to rewrite the query in consideration of 'NA' rather than TM1 attempting to handle 'NA' (simply because it still kicks out some values that are supposed to go into 'NA', but not all).

Thanks again!