TI Process to sort dimensions elements in numerical/alphabetical order
Can you help with the elements of a dimension.
One dimension that I use like a identity in my cubes, to convert the records in unique.
But those elements are sort in alphabetical order instead of numerical order, i.e.:
The Order into the dimension is: 1,10,11,12,100,101,1000,1001,2,3,4...
But I need the followin order (numerical order): 1,2,3,4...10,11,12...,100,101...,1000,1001...
All those elements are Simple elements (n). See the attachment.
I modify the turbo integrator process to convert my elements in text to fill all left positions wiht zeros, like that:
To order it...
Can you help telling me how can create that process en Turbo Integrator?
Thank you for your guidance...
Attachment: Screenshot 1.jpg
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
4 replies Latest Post - 2012-11-01T14:10:35Z by Jose.Mong
Pinned topic TI Process to sort dimensions elements in numerical/alphabetical order
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-01T14:10:35Z at 2012-11-01T14:10:35Z by Jose.Mong
Rizwan.Kaif 27000219XW197 PostsACCEPTED ANSWER
Re: TI Process to sort dimensions elements in numerical/alphabetical order2012-11-01T03:38:09Z in response to Jose.MongThere might be different ways of doing this. One way which I can think of now is that:
1)Convert the number to a string using NumberToString() function in TI. This will convert 10 to string "10"
2)Then use the LEN() function to get the length of the string. This will return you length of 2 for string"10"
3)Use the IF condition to append zeros before the string
IF (LEN(myString) = 1); myString = "0000" | myString; ELSEIF (LEN(myString) = 2); myString = "000" | myString; ELSEIF (LEN(myString) = 3); myString = "000" | myString; ELSEIF (LEN(myString) = 4); myString = "00" | myString; ELSEIF (LEN(myString) = 5); myString = "0" | myString; ENDIF;
Hope that helps!
SystemAdmin 110000D4XK658 PostsACCEPTED ANSWER
Re: TI Process to sort dimensions elements in numerical/alphabetical order2012-11-01T05:38:07Z in response to Jose.MongHi Jose,
Similar to Rizwans solution, convert the number to a string then add the padding then set the sort order. Note in the following sample (MetaData tab), V1 is the variable name on the Variable tab and sDim is a variable holding the dimension name. If you need additional padding in the element name then increase the value of 4 in the Fill statement.
#****Put padding in front of element****
sElement = NumberToString(V1);
sElement = Fill('0', 4 - Long(sElement)) | sElement;
#****Add element to dimension****
DimensionElementInsert(sDim, '', sElement, 'N');
#****Fix sort order****
DimensionSortOrder(sDim, 'ByName', 'Ascending', 'ByName', 'Ascending');
Hope this helps.