If null in TM1 rule

[gdimit]

I want to write a rule that checks if the cell is null or not.

My problem is that either the cell is null or 0 I have the same result.

Ideally I would like the calculation not to be performed if the cell is null and to be performed if the cell has 0.

Any recommendations?

[declanr]

I want to write a rule that checks if the cell is null or not.

My problem is that either the cell is null or 0 I have the same result.

Ideally I would like the calculation not to be performed if the cell is null and to be performed if the cell has 0.

Any recommendations?

Have a scan over the documentation for UNDEFVALS and Feeders, it should get you pointed in a possibly right direction.

[Steve Rowe]

gdimit,
I'm pretty interested what purpose you would put this to?

I've never been in a position to need to know the difference between 0 or null or to know if a cell is null or not. What would the practical application of doing what you want?

Cheers,

[lotsaram]

gdimit,
I'm pretty interested what purpose you would put this to?

I've never been in a position to need to know the difference between 0 or null or to know if a cell is null or not. What would the practical application of doing what you want?

Cheers,

Likewise. Although the team behind the new Performance Modler seem to be from the school of thought that differentiating between NULL versus 0 is essential and so insert UNDEFVALS into every rule file to me this seems a philosophical as opposed to practical position. In many dozens of real world implementations I am yet to come across a single case where in fact there was any need to differentiate between "zero" and "blank".

[TrailRunnerMark]

Since TM1 doesn't store zero's (unlike other databases) there is no difference between Null and 0; they are the same thing. If it is a rule calculated value that is conditionally populated (so it might be populated and that value could be zero) you could base your test on the condition used in the calculation to decide if the cell is populatable and then check if the calculation gave a zero or non-zero value.

But I suspect that isn't the case for you.

The other option is to store something other than zero for those cells you want to be zero. You could define the element as string and store all numbers as strings. Then a zero would be different from blank. Makes your calculations a bit more complicated as you have lots of conversion from string to number and back, but that should work.

[paulsimon]

Hi

I would avoid UNDEFVALS simply on the basis that it is a little used facility in TM1, and using it is therefore likely to have unexpected consequences.

Another approach is to value two measures:

Value
Not Null

When loading data from your relational database if the value is 0, you put a 1 in to the Not Null measure.

Then your rules just need to say

IF( [Value] <> 0 % [Not Null] <> 0
,
Do calc
,
0
) ;

However, philosophically there are at least three possible meanings for the Null

a) Other values for the user have been collected but this value is not known, eg they did not want to reveal their age
b) This value is not applicable miles per gallon on an electric car
c) This value has not been populated yet - empty cell combinations in a cube

Potentially you could use the extra measure to distinguish between these, if appropriate.

Regards

Paul Simon

[gdimit]

gdimit,
I'm pretty interested what purpose you would put this to?

I've never been in a position to need to know the difference between 0 or null or to know if a cell is null or not. What would the practical application of doing what you want?

Cheers,

What I want to store is football results.

So in the games there r 0s



From the matches I would like to calculate the score and the points of each team.
Since I have loaded all the match programme there are games that have null (games that are not played yet).
So I d like to differientate the matches that their score is 0 - 0 so in the cells are 0 from the those that r not played yet in which there cells are nulls


@Paul Simon

Unfotunately I do not want to load data. I procceed with a solution similar to yours. I have place a drop measure down measure yes/no which indicated if the match was played or not.
Thanks for your response.