ISLEAF Function for TI

[Kingsley]

Is there a function for TI equivalent to the ISLEAF rule function?
If I needed to check in the Metadata/Data tab whether the referenced cell is a leaf level cell or not, is there a more pratical way than:

Code: Select all

IF((ELLEV('Dim1',vDim1) = 0) & (ELLEV('Dim2',vDim2) = 0) & (ELLEV('Dim3',vDim3) = 0));

?

[jim wood]

What does your data source look like to have to try to do dim1, then dim2 etc... ElLev however is normally the weapon of choice, it's just a case of how you deploy it.

[Kingsley]

The datasource is a text file export of a cube. Error occurs on non-leaf cells when trying to input the data back into the cube via CellPutN().

Using ELLEV seems to be faster than CellIsUpdateable()
Good to know that this is the way to do it.

Thanks

[Wim Gielis]

Dtype('dimension','element')@='N'

is an alternative to Ellev. Don't know if it's faster or not. I'll leave that one as an exercise to you

[lotsaram]

You could use CellIsUpdateable( Cube, dim1, dim2, ... dimn )
It will return true (=1) if all dimension references are leaf elements, which would be a shortcut to testing ELLEV or DTYPE of all dimensions individually.

But if there is a rule on the cell or locks on any of the elements it will return false. So not quite the same as ISLEAF but might be close enough depending on your situation.

[Wim Gielis]

In the end, if you want to do a CellPutN (or CellIncrementN, CellPutS, for that matter), you're interested in being able to update the cell or not.
Be it rules-driven cells, consolidated cells, cells with a lock, ... CellIsUpdateable is one possibility.

If it turns out that CellIsUpdateable returns zero for some record(s), you might want to populate an error log (CSV file for example) or use ItemReject (or just ItemSkip).
Part of the ItemReject could be that Ellev > 0 for some field.

[rmackenzie]

Dtype('dimension','element')@='N'

is an alternative to Ellev. Don't know if it's faster or not. I'll leave that one as an exercise to you

DTYPE is preferred to ELLEV for the purposes of the OP - a consolidated element with no children will return an ELLEV=0 but also should mean CellIsUpdateable is 0. However, its DTYPE is C as opposed to N. E.g.

Code: Select all

IF ( DTYPE ( 'Dim1', 'Elem1' ) @= 'N' & DTYPE ( 'Dim2', 'Elem2' ) @= 'N' ... etc)

[Michel Zijlema]

The datasource is a text file export of a cube. Error occurs on non-leaf cells when trying to input the data back into the cube via CellPutN().

Was there a reason to include the consolidated cells in the file export? When there are no consolidated cell values in the export you wouldnt need to bother about celltypes.

Michel

[jim wood]

The datasource is a text file export of a cube. Error occurs on non-leaf cells when trying to input the data back into the cube via CellPutN().

Was there a reason to include the consolidated cells in the file export? When there are no consolidated cell values in the export you wouldnt need to bother about celltypes.

Michel

A very good point. Even if you don't dimension parity you can still make sure no consolidations exist in the dimensions that you're directly mapping. Saying that, we don't know if it's an export from a TM1 cube. The cube may be somewhere else, like EP or Essbase. Although the same principal should still apply.

[Wim Gielis]

DTYPE is preferred to ELLEV for the purposes

I agree.

a consolidated element with no children will return an ELLEV=0

I don't agree. I am using version 10.2 and it returns 1.

[lotsaram]

That must be new with 10.2 or maybe 10.1
Certainly always used to be the case that an orphan C element returned 0 for the level. It's both good that they've fixed this yet somehow also not quite logical.

[Wim Gielis]

It was my belief as well, that it returned 0. That's why I mentioned the difference in (at least) 10.2

[rmackenzie]

Wim, thanks for the update. That's a little surprising that that change got implemented... I am also wondering if it is totally logical...

[Duncan P]

ELLEV returns 1 for an empty C in 9.5.2 as well. Which version have you seen it return 0 in?

I would have thought that 1 is more sensible, particularly if you are using filter by level to select leaves. As in "It's got no children, but all of them are leaves."

[EDIT] Oh and BTW it returns 1 in 9.0 SP3 as well. Perhaps it was a temporary thing in 9.1 or 9.4 that someone complained about.

[rmackenzie]

[EDIT] Oh and BTW it returns 1 in 9.0 SP3 as well. Perhaps it was a temporary thing in 9.1 or 9.4 that someone complained about.

It happened on a number of occasions but unfortunately I can't recall which versions - all probably 9.x. Notably it cropped up in this sort of statement:

Code: Select all

{TM1FILTERBYLEVEL({TM1SUBSETALL([Some_dimension])}, 0)}

as well as with the ELLEV function.

I got used to using DTYPE to isolate n-level leaves and had assumed that the empty parents being at level 0 was by design and not in error, as now seems the case with the anomalous return values we're talking about.

[Wim Gielis]

For me as well, there have been version(s) where the result was 0 for the ellev, certainly 9.x version(s), perhaps around 9.4 a couple of years ago.
Sorry I can't be that precise anymore Time flies when you're having fun