TM1 Forum

Hi All,

I've a requirement to sort the data in an exported ascii file based on values in two of the columns (Trade Number and Currency). Both these columns are dimensions in the cube for which data is being exported.

The current output is as shown below The output is required to be sorted based on the highlighted columns as shown below I tried creating a view with the order of dimension subsets as required in the output but that didn't help. Then I tried to sort the data in the ascii file using batch files. I could sort the data based on trade number but it is text sorted and not numeric sort.

Can anyone please help me on this?

Thanks in advance.
Aarti

Hello Aarti

That seems very similar to what I encountered some time ago:
http://users.skynet.be/fa436118/wim/tm1 ... utsort.htm

And also see the comments at:
http://blog.tm1tutorials.com/2011/10/12 ... im-gielis/

Did you use the DOS command, and maybe you need a more clever/sophisticated way to sort the data?

Wim

Hi Wim,

Thanks for your reply!

I did use the DOS command to sort the data. It does sort the data, but that is text sort ie. the numbers are treated as string and then sorted, which is not the desired output.

I want to achieve a numeric sort and to do it the way we have in excel, with levels.

Note: The ascii file has data in 74 columns

Thanks

There is no facility in TM1 to perform what you want. You will have to add another layer to the process, like outputting the TM1 data to a flat file, and then have your own custom VB program to do the sorting you want and to render the final file.

Hi Tomok,

I'm exporting the data to a flat file...But the problem is we do not use VB in our applications. So I need to find a way to do it without using VB.

Can anyone throw light on how TM1 writes data to an ascii file?...I guess it does using the dimension element index coz sorting the dimension subsets in the cube view didn't help too.

Aarti

RT1107 wrote:Can anyone throw light on how TM1 writes data to an ascii file?...I guess it does using the dimension element index coz sorting the dimension subsets in the cube view didn't help too.

It would have worked if these were your first and second dimension in that cube, respectively. The order in which TI processes records from a source view is dependent on the order of dimensions in the cube and then on the order of elements in the subsets that define your source view.

I know this is a "low tech" and rather obvious suggestion but have you considdered padding the fields with leading zeros to a defined character length? Then an alpha sort versus a numeric value sort would both produce the same order being the one that you want.