Sorting a TI subset w/o using MDX

[terry720]

Okay, please don't laugh at me because I'm not a coder..but I played with this for a long time and this is as far as I got. I figured someone out here could help mature it based on what I've got.

Basically, I create a temp subset with all my elements in it....then I run this code to create a final subset with the elements in Alphabetic order. Actually, it only groups the first letter...but that's where you guys come in.

I think this could really help a lot of people if we can make it work for entire element names. Line 22 converts the first letter of the source subset job and the destination subset job into ASCII value and compare to determine if it's bigger or smaller. That if smaller, it inserts it in the destination subset in that spot...if bigger, it loops around and checks the next job in the destination subset. It continues this until it finds a job with a higher first letter.

I think what it needs to do next is check the second letter and if bigger...move on to the next job and start over with letter 1. It should cycle through every letter in the 'tJob' to put it in the exact right spot before moving on to the next job in the temp subset.

Code: Select all

jDim = 'Job Dimension';
tSubset = 'Temp Job Subset';
sSubset = 'Sorted Job Subset';

IF (SubsetExists( jDim, sSubset )=1);
    SubsetDestroy( jDim, sSubset );
ENDIF;

SubsetCreate( jDim, sSubset );

tSubCount = SUBSETGETSIZE( jDim , tSubset);
tIndex = 1;
While ( tIndex <= tSubCount );
    vJob = SUBSETGETELEMENTNAME( jDim, tSubset, tIndex );
    sSubCount = SUBSETGETSIZE( jDim, sSubset );
    IF ( sSubCount = 0);
        SUBSETELEMENTINSERT( jDim, sSubset, tJob, 1 );
    ELSE;
    sIndex = 1;
    While ( sIndex <= sSubCount );
        sJob = SUBSETGETELEMENTNAME( jDim, sSubset, sIndex );
        IF ( CODE ( tJob, 1 ) < CODE ( sJob, 1 ) );
            SUBSETELEMENTINSERT( jDim, sSubset, tJob, sIndex );
            sIndex = sSubCount + 1;
        ELSE;
            IF ( sIndex = sSubCount );
                SUBSETELEMENTINSERT( jDim, sSubset, tJob, sIndex + 1 );
                sIndex = sSubCount + 1;
            ELSE;
                sIndex = sIndex + 1;
            ENDIF;
        ENDIF;
    END;
    ENDIF;
    tIndex = tIndex + 1;
END;

[Wim Gielis]

Or...

Creating a (temporary) dimension, populate it and if the sort order of the dimension is set properly... you will have much easier code in your TI process

[Harvey]

Terry, I'd be curious to know what reason you have for needing such functionality? Usually the SubsetCreateByMDX function would do this job much more easily. This is just because I've never come across this need and I've worked on some crazy projects in my time!

Are you aware that you can use the @< and @> operators on strings to determine which one is higher in the sort order?

For instance:

Code: Select all

IF('ABC' @> 'DEF');
    ASCIIOUTPUT('c:\temp.txt', 'yes');
ELSE;
    ASCIIOUTPUT('c:\temp.txt', 'no');
ENDIF;

This will create a text file with the value "no" printed in it.

I'm pretty sure you could use this technique to do your sort more easily than comparing individual characters, but you will not have direct control over which string is considered "larger" than another, and perhaps you need that?

[lotsaram]

I'm always surprised how few people know about and use @>= and @<= string comparison operators in TM1 but I think the easiest method to solve the problem is as suggested by Wim, create a temp dim with flat structure sorted by name then read from the temp dim into the final subset. As a similar alternative rather than a temp dim export to a text file then sort the contents with a batch file then read back in.

[terry720]

I was not aware of the @> and @< functionality. I will try that. As I said, I'm not a coder...I'm a finance user who dabbles a bit on the side.

As for why I'm not using an MDX formula...it's because I am using the created subset in other processes and calls from a custom website we use to control out job hierarchy and attributes. There are significant performance impacts associated with using MDX expressions. So much so...that on guidance from IBM we have essentially banned the use of them.

I actually tried using an MDX to create a temporary subset...and then create a final non-MDX using the elements of the MDX subset. The problem is that you have to copy the elements from the MDX one at a time and insert them into the non-MDX subset. Each time you go back to the MDX subset...it re-evaluates...and slows the process down.

What I'm doing now creates all the subsets I need in less than a minute...they're just not sorted right. Using the MDX to create them sorted..then freezing each of them off in a non-MDX subset took almost an hour. This is something we have to do every day to pick up hierarchy changes so an hour isn't going to work.

[qml]

There is a rather neat trick you could use here instead of all the redundant code. If you add an element to a dynamic subset using SubsetElementInsert() it becomes a static subset. If you then delete the dummy element you've just added, you end up with a static version of the original MDX subset having written just two lines of code.