Numeric sort on Ascii file output

Post Reply
RT1107
Posts: 7
Joined: Fri Oct 30, 2009 6:07 am
OLAP Product: tm1
Version: 9.4 and 9.5
Excel Version: 2007

Numeric sort on Ascii file output

Post by RT1107 »

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
Unsorted output.jpg
Unsorted output.jpg (59.61 KiB) Viewed 4740 times
The output is required to be sorted based on the highlighted columns as shown below
Reqd sorted output.jpg
Reqd sorted output.jpg (52.23 KiB) Viewed 4740 times
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
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Numeric sort on Ascii file output

Post by Wim Gielis »

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
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
RT1107
Posts: 7
Joined: Fri Oct 30, 2009 6:07 am
OLAP Product: tm1
Version: 9.4 and 9.5
Excel Version: 2007

Re: Numeric sort on Ascii file output

Post by RT1107 »

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
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Numeric sort on Ascii file output

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
RT1107
Posts: 7
Joined: Fri Oct 30, 2009 6:07 am
OLAP Product: tm1
Version: 9.4 and 9.5
Excel Version: 2007

Re: Numeric sort on Ascii file output

Post by RT1107 »

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
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Numeric sort on Ascii file output

Post by qml »

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.
Kamil Arendt
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Numeric sort on Ascii file output

Post by lotsaram »

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.
Post Reply