Asciioutput issue
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Asciioutput issue
Hi
I am trying to Extract data from a cube view , When extracting to CSV file i have Print heading aswell in the CSV file, So i am trying to write the below mentioned code to print the values.
If(count=1);
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
count=count+1;
Else;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
endif;
Endif;
Can any one help on this, If you need more information let me know so that i can provide .
Regards
Ashok
I am trying to Extract data from a cube view , When extracting to CSV file i have Print heading aswell in the CSV file, So i am trying to write the below mentioned code to print the values.
If(count=1);
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
count=count+1;
Else;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
endif;
Endif;
Can any one help on this, If you need more information let me know so that i can provide .
Regards
Ashok
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
sorry, I for got to include the important point, I am able to extract the data into the CSV file but the data is coming Tiwce (Each Row it is repeating)
-
- MVP
- Posts: 263
- Joined: Fri Jun 27, 2008 12:15 am
- OLAP Product: Cognos TM1, CX
- Version: 9.0 and up
- Excel Version: 2007 and up
Re: Asciioutput issue
Hi
Put a
count = 0;
in the Prolog tab and move your
count=count+1;
to the very top of your data tab (where you have your asciioutput).
Put a
count = 0;
in the Prolog tab and move your
count=count+1;
to the very top of your data tab (where you have your asciioutput).
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
I tried - Not working still the rows are repeating:
If(count=1);
count = count+1;
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
Else;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
endif;
Rgd's
Ashok
If(count=1);
count = count+1;
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
Else;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
endif;
Rgd's
Ashok
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Asciioutput issue
Sorry to ask the obvious but have you checked your data source to make sure that there aren't the "duplicate" rows you are getting in the csv?AshokReddy wrote:sorry, I for got to include the important point, I am able to extract the data into the CSV file but the data is coming Tiwce (Each Row it is repeating)
Declan Rodger
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
I am extracting this data from a cube, So there won't be any duplicates in the cube.
RGD's
Ashok
RGD's
Ashok
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Asciioutput issue
A small correction to your code first
If(count=1);
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
count=count+1;
EndIf;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
If you do your asciioutput in the else of the header then you will skip the first row of the data source.
Regarding your export from the cube it is possible to get duplicate records from the cube if the subsets in your view are set up incorrectly. For example if you are exporting Mar and you put a subset with Mar in it twice in your export view then you will get double records.
So if you are using subsets in your view export check them for duplicates.
Cheers
If(count=1);
ASCIIoutput(v_output_file,'FISCAL PERIOD','PLANT','MATERIAL OUT','MATERIAL','ACTIVITY TYPE','COST CENTER','DEBIT CREDIT','CURRENCY','UoM','BUDGET
AMOUNT','BUDGET QUANTITY');
count=count+1;
EndIf;
ASCIIoutput(v_output_file,v_fiscal_period,v_Plant,v_material_out,v_material_in,v_Activity_Type,v_cost_center,v_debit,v_currency,v_UoM,numbertostri
ng(BGT_AMT),numbertostring(BGT_QTY));
If you do your asciioutput in the else of the header then you will skip the first row of the data source.
Regarding your export from the cube it is possible to get duplicate records from the cube if the subsets in your view are set up incorrectly. For example if you are exporting Mar and you put a subset with Mar in it twice in your export view then you will get double records.
So if you are using subsets in your view export check them for duplicates.
Cheers
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Asciioutput issue
If you are reading from a cube then you get a row for each cell in the view. You don't get multiple measures. So my question is how are you populating the variables BGT_AMT and BGT_QTY?
On the basis of the information you have posted I suspect that you are getting a row for each amount cell and a row for each quantity cell, but that somehow you are getting the values for both of these from somewhere else, perhaps with CellGetN.
On the basis of the information you have posted I suspect that you are getting a row for each amount cell and a row for each quantity cell, but that somehow you are getting the values for both of these from somewhere else, perhaps with CellGetN.
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
Hi
Yes, I am getting the BGTQTY and BGTAMT using CELLGETN
Yes, I am getting the BGTQTY and BGTAMT using CELLGETN
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Asciioutput issue
You need to restrict your source view to either BGT_QTY or BGT_AMT but not both.
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
@Duncan -- Thanks a lot for your expert comments..
It worked for us.. and now in output file we are getting single records.
But, could you please explain it in little bit more detail.
According to our understanding:
While creating source view of the cube, we were using 2 elements of measure dimension for creating the subset.
So while extracting as per you,we will be getting a row for each cell of the view( due to which we were getting 2 records)
Now if i test with 3 elements in measure dimension, still i am getting 2 records in extracted file instead of 3 records.
Appreciate your help , please explain so that we can understand better.
Once Again Thanks for your help Duncan...!!!!!
It worked for us.. and now in output file we are getting single records.
But, could you please explain it in little bit more detail.
According to our understanding:
While creating source view of the cube, we were using 2 elements of measure dimension for creating the subset.
So while extracting as per you,we will be getting a row for each cell of the view( due to which we were getting 2 records)
Now if i test with 3 elements in measure dimension, still i am getting 2 records in extracted file instead of 3 records.
Appreciate your help , please explain so that we can understand better.
Once Again Thanks for your help Duncan...!!!!!
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Asciioutput issue
It maybe that your third measure has no value in it?
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 12
- Joined: Wed Jul 25, 2012 6:15 am
- OLAP Product: Cognos TM1
- Version: 9.4.1
- Excel Version: 2003
Re: Asciioutput issue
Thanks guys for all your support.!!!!!
Ahhh... Got your point...
@Duncan - Your comment is completely understood..
Thanks Duncan and Steve Rowe. .....
Ahhh... Got your point...
@Duncan - Your comment is completely understood..
Thanks Duncan and Steve Rowe. .....
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Asciioutput issue
If you are skipping zero values, as it seems you are, then you need to be careful which measure you choose, to be sure that it will be non-zero when either BGT_AMT or BGT_QTY is non-zero.
It might be an idea to create a calculated measure (called e.g. 'Non-zero record') with the statement :-
Then you should have only 'Non-zero record' selected in your source view. You can still get the values of the measures with CellGetN as you do now.
This point is covered in more detail in this thread here http://www.tm1forum.com/viewtopic.php?f=3&t=7439.
It might be an idea to create a calculated measure (called e.g. 'Non-zero record') with the statement :-
Code: Select all
['Non-zero record'] = N: IF( 0 <> ['BGT_AMT'] % 0 <> ['BGT_QTY'], 1, 0 );
FEEDERS;
[{'BGT_AMT','BGT_QTY'}] => ['Non-zero record'];
This point is covered in more detail in this thread here http://www.tm1forum.com/viewtopic.php?f=3&t=7439.