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 » Mon Oct 08, 2012 8:37 pm

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 1979 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 1979 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: 2262
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Numeric sort on Ascii file output

Post by Wim Gielis » Mon Oct 08, 2012 10:07 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

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 » Tue Oct 09, 2012 2:36 pm

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: 2638
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 » Tue Oct 09, 2012 3:44 pm

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 » Wed Oct 10, 2012 3:34 pm

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: 1078
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Numeric sort on Ascii file output

Post by qml » Wed Oct 10, 2012 3:40 pm

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: 3320
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 » Wed Oct 10, 2012 6:42 pm

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