TM1 Forum

Hi Everyone,
One of the sql table fields to be used as dimension has null value in it. I doubt we can create a 'null' element for the dimension. If that's the case, I can't really do cellputs or cellputn to insert the data into an intersection with null element, right? For example, if I do CellPutN(data, Cube, e1, e2, e3, e4) where e2 has a null element, then it won't work, right?
So what can I do about this?

Lee.

That depends on the data and what you want to do with it. You can do pretty much anything you want with TI.

If you can live with just not including that data in your TM1 model then you can just check for nulls with an IF statement and do an ItemSkip (or just get rid of the nulls as part of your SQL query.)
If you do still want to see the number adding into your totals then add a dimension element called "Null" or "Dummy" etc. (that would be the word "Null" not the relational database sense of a null) and do an IF statement that checks if your variable is a null and if so declares it as "Null" or "Dummy" etc instead.

Lee,
Another approach would be to use the nvl funcion in sql to populate the nulls with you default element to eliminate any evaluations in Ti. ie NVL(FieldName, 'Not Available')

Thank you all for the reply.