Page 1 of 1
ISLEAF Function for TI
Posted: Thu Dec 12, 2013 7:53 pm
by 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));
?
Re: ISLEAF Function for TI
Posted: Thu Dec 12, 2013 8:17 pm
by 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.
Re: ISLEAF Function for TI
Posted: Thu Dec 12, 2013 8:26 pm
by 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
Re: ISLEAF Function for TI
Posted: Thu Dec 12, 2013 8:54 pm
by 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
Re: ISLEAF Function for TI
Posted: Thu Dec 12, 2013 9:25 pm
by 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.
Re: ISLEAF Function for TI
Posted: Thu Dec 12, 2013 10:11 pm
by 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.
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 2:17 am
by rmackenzie
Wim Gielis wrote: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)
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 8:29 am
by Michel Zijlema
Kingsley wrote: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
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 2:14 pm
by jim wood
Michel Zijlema wrote:Kingsley wrote: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.
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 7:30 pm
by Wim Gielis
rmackenzie wrote:DTYPE is preferred to ELLEV for the purposes
I agree.
rmackenzie wrote: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.
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 8:06 pm
by 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.
Re: ISLEAF Function for TI
Posted: Fri Dec 13, 2013 8:31 pm
by Wim Gielis
It was my belief as well, that it returned 0. That's why I mentioned the difference in (at least) 10.2
Re: ISLEAF Function for TI
Posted: Mon Dec 16, 2013 4:55 am
by 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...
Re: ISLEAF Function for TI
Posted: Mon Dec 16, 2013 11:39 am
by 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.
Re: ISLEAF Function for TI
Posted: Mon Dec 16, 2013 12:13 pm
by rmackenzie
Duncan P wrote:[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.
Re: ISLEAF Function for TI
Posted: Mon Dec 16, 2013 11:33 pm
by 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
