UNDEFVALS - only in there for the ticklist?

[Michel Zijlema]

I've been testing the UNDEFVALS functionality lately (using TM1 9.5.1) and by now I can't imagine that this functionality is of use to anyone.
Main reason for this is that it seems to be impossible to return a populated cell (containing either a non-zero or zero value) to an unpopulated state (containing the 'UNDEF' value) - apart from deleting/recreating the cube or restoring a backup (you can force cells with a rule, but than the cell is not writable anymore and the cell is still visible in a zeroed out view).
I wanted to investigate the effect of UNDEFVALS on data spreading - f.i. whether the Repeat Leaves function applied to all populated cells would also write to cells containing a zero. But unfortunately the Repeat Leaves on populated cells will skip both empty cells and cells populated with zeroes.

Am I missing something here? Is anyone using this functionality and is satisfied with how it works?


Michel

[John Hammond]

Undefvals is clearly an attempt to introduce the 'null' concept in RDBMS's.

If you were building TM1 from scratch now clearly you would make the empty cells contain a null value and not zero so you could store a zero value legitimately. Unfortunately the implementation is half hearted and like you I tried to play with this to see if it was a useful technique and I could not get it to work.

I think I tried (IIRC) [area] = undefvals ; and I think you get a compile error.

I think there was never the critical mass of user pressure to get this to work although I have found problems where you add values together to make 0 and the result disappears from the input of a TI. In retrospect it is logical that it would do this but when you add A with 100 values to B with 100 values and only get 99 values in your TI it is very surprising. So you hit the occasional problem and you find some other way to solve this.

[Duncan P]

If you have a small test cube with UNDEFVALS and you export it having unchecked the 'Skip Zero/Blank Values' box, you will get a file full of the number 4.94066e-324.

If you enter this number into a cell then TM1 will blank it when you recalculate and remove it the next time you save.

[Alan Kirk]

If you have a small test cube with UNDEFVALS and you export it having unchecked the 'Skip Zero/Blank Values' box, you will get a file full of the number 4.94066e-324.

If you enter this number into a cell then TM1 will blank it when you recalculate and remove it the next time you save.

Interestingly, a year and a bit later, a Technote was released on that very issue. It can be found here.