SUBIX function

Suggest and discuss enhancements for TM1
Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1957
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

SUBIX function

Post by Martin Ryan » Thu Jan 21, 2010 8:26 pm

I thought it'd be a good idea to raise the SUBIX in this forum in case it's monitored by our IBM friends as it's come up for me again today and has been mentioned a couple of times in the old forum, including at least one formal enhancement request from Duncan T back in 2005.

John's got his undo spread, surely the subix is next on the list!

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

User avatar
Alan Kirk
Site Admin
Posts: 5874
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: SUBIX function

Post by Alan Kirk » Thu Dec 18, 2014 2:41 am

Martin Ryan wrote:I thought it'd be a good idea to raise the SUBIX in this forum in case it's monitored by our IBM friends as it's come up for me again today and has been mentioned a couple of times in the old forum, including at least one formal enhancement request from Duncan T back in 2005.

John's got his undo spread, surely the subix is next on the list!

Martin
Just for reference I logged this on the IBM RFE site as 63581.

Description
For over 10 years the user community has been calling for a SubIx function in TurboIntegrator which operates similarly to the DimIx function.

The principal need for this is to ensure that;
(a) When a subset is dynamically created from a data source; and
(b) That data source may have multiple occurrences of an element in different rows; then
(c) The resulting subset will contain an element name once and once only.

At present this can only be achieved by kludgy and time consuming workarounds like iterating the entire subset to check for the existence of the current row's element, or writing the elements into a temporary dimension where we know that the list will be unique, and then creating the subset from that dimension.

Since an element can in fact occur more than once in a subset the SubIx function would probably need an argument to specify which occurrence of the element that you're seeking and return the index accordingly.

At a pinch, if a SubIx function is not possible then a SubsetElementExists function which returns 1 if the element is ANYWHERE in the subset and 0 if it is not would at least overcome the most common problem associated with the absence of this functionality.
Use case
Rather than offer one of my own I'll refer to one that Duncan Turner submitted to Applix on 19 May 2005 (coming up to 10 years ago), for users the world over have had the same problem for the same amount of time:

I am building a subset from a repeating source file e.g. multiple rows contain the same element.

When building a dimension, no duplicates are allowed and so the build is clean. However, when building a subset, each element is inserted one-for-one, resulting in a subset with as many elements as the source has rows.

In order to get a "clean" subset, I am having to either build a temp dimension and then a subset, or loop the subset to check for an element's existence.

If SUBIX were available, I could perform the usual IF(SUBIX(dim,elem) > 0 type of argument which would save time and effort.
Business justification
It is ridiculous that we need to be spending time and effort to create workarounds to TI language deficiencies which SHOULD be solved with a single function call. The time taken to overcome missing functionality is not massive in any one individual case but it adds up, repeatedly. The SubIx problem is a very common one, which means that the total amount of time spent by TM1 administrators in working around it, and TI processes in executing unnecessary loops or temporary dimension creation, rapidly runs into days, weeks, months.

More attention needs to be paid to the underlying language structure so that we can get to where we need to faster.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
jim wood
Site Admin
Posts: 3658
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: SUBIX function

Post by jim wood » Thu Dec 18, 2014 1:00 pm

+1 from me. Hopefully that will at least mean something to big blue?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Post Reply