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
Numeric sort on Ascii file output
-
- 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
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
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
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
-
- 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
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
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
-
- 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
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.
-
- 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
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
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
- 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
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.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.
Kamil Arendt
-
- 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
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.