TM1 Forum

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

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)

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).

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

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)

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?

I am extracting this data from a cube, So there won't be any duplicates in the cube.

RGD's
Ashok

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

Hi

Yes, I am getting the BGTQTY and BGTAMT using CELLGETN

You need to restrict your source view to either BGT_QTY or BGT_AMT but not both.

@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 maybe that your third measure has no value in it?

Thanks guys for all your support.!!!!!

Ahhh... Got your point...

@Duncan - Your comment is completely understood..

Thanks Duncan and Steve Rowe. .....

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.