TM1 Forum

Posted: **Mon Mar 27, 2017 5:44 am**

Recently I encountered a similar scenario ........
say a dimension name MEASURE
A (parent)
----- B (child1)
----- C (child2)

A = B -C

while exporting the data to csv file for all PRODUCT dimension elements.... I selected the top level of dim MEASURE (ie element A)
but the records for the PRODUCT elements having value for MEASURE B & C got excluded from the source view as I used the following code -

ViewExtractSkipCalcsSet (CubeName, ViewName, 0);
ViewExtractSkipZeroesSet (CubeName, ViewName, 1);
ViewExtractSkipRuleValuesSet(CubeName, ViewName, 0);

can't ignore ZERO SUPPRESS as cube/view size is already very heavy.....

Output file doesnt have records for those PRODUCT elements where B = C (MEASURE DImension)
I want all those PRODUCT elements to be in output csv file, which have data for MEASURE B and C.

===== PROLOG TAB =====
Creating source view
suppose Cube has 5 dimensions - COUNTRY, PRODUCT, YEAR, MONTH, MEASURE
while creating source view I selected the following (using SubsetCreate & SubsetElementInserrt functions ) -

COUNTRY - Country (top level)
PRODUCT - all leaf level elements using MDX Query
YEAR - pYear ( from parameter)
MONTH - pMonth (from parameter)
MEASURE - A (top level)

what could be the alternative approach, to get the desired result. (ie. All PRODUCT elements having non Zero values)

Posted: **Mon Mar 27, 2017 6:15 am**

Did you really read the other contributions in this topic ?
Did you try them ?

Posted: **Mon Apr 03, 2017 2:12 pm**

Let feeders be your friends. http://www.tm1forum.com/viewtopic.php?f=3&t=7439

Feeders will not suffer from the cancelling out of normal accumulation.

Posted: **Tue Jul 16, 2019 8:43 pm**

Hey guys!

I am facing exactly this same problem right now, but I did not understand the flag point.

I am using ASCIIOutPut and I want months on columns.

I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.

I didn't get what you have done about using flag and the total on view data source.

Can anyone explain that for me?

Thanks a lot.

JR.

Posted: **Tue Jul 16, 2019 9:28 pm**

Jorge Rachid wrote: ↑Tue Jul 16, 2019 8:43 pm I am using ASCIIOutPut and I want months on columns.

I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.

I didn't get what you have done about using flag and the total on view data source.

Can anyone explain that for me?

I could just go with Wim's earlier suggestion for someone else.

Wim Gielis wrote: ↑Mon Mar 27, 2017 6:15 am Did you really read the other contributions in this topic ?
Did you try them ?

But assuming you did actually read the other posts I will try and keep it very simple.

You have a TI process which you control. You can edit it!

- You need to change the data source to read only "Total Year". DON'T have all the months in the source view.
- Then have 12 x CellGetN on your data tab to read in the month values. Assign each to a separate variable
- Then in the AsciiOutput write out the 12 values. Ignore the Total Year value (or include it as a 13th column)

Code: Select all

sValJan = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Jan', 'Value' ) );
sValFeb = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Feb', 'Value' ) =;
...
sValDec = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Dec', 'Value' ) );

AsciiOutput( myFile, sValJan, sValFeb, ... sValDec );

Although now I actually did quickly scan the previous posts and I don't think I offered anything new or and simpler than what was said already (years ago).

Posted: **Tue Jul 16, 2019 10:01 pm**

Hi Jorge

If by flag you are referring to the cFlag in the 6th post, that is actually nothing to do with getting the 12 months. Read the posts for that.

The cFlag is only there to output a heading row. Personally I wouldn't use a c prefix since that implies that it is a constant to be but it is being modified so it is a variable, however, we all have our own naming conventions.

The logic could be simpler. There is no need for a -1, eg

Prolog

iRowCount = 0 ;

Data Tab

iRowCount = iRowCount + 1 ;

IF( iRowCount = 1 ) ;
AsciiOutput( sPathFile, 'Account' , 'Cost Centre' , whatever headings are applicable to your file ) ;
ENDIF ;

Rest of logic deals with outputting the 12 values across the columns.

You could just use a variable as a flag eg bFirstRow = 1 on Prolog, then on Data Tab IF( bFirstRow = 1) ; output headings, followed by bFirstRow = 0 ENDIF. However, the advantage of using a RowCount is that this also gives you a count of the records that you processed which you can output in the Epilog to a logging cube.

Regards

Paul Simon

Posted: **Tue Jul 16, 2019 10:34 pm**

I did that, but now it is coming 16 rows with repeated values.

Here my code:

Code: Select all


vJan = NumberToString(CellGetN('base_despesas_rateio', '01/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vFev = NumberToString(CellGetN('base_despesas_rateio', '02/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vMar = NumberToString(CellGetN('base_despesas_rateio', '03/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vAbr = NumberToString(CellGetN('base_despesas_rateio', '04/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vMai = NumberToString(CellGetN('base_despesas_rateio', '05/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vJun = NumberToString(CellGetN('base_despesas_rateio', '06/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vJul = NumberToString(CellGetN('base_despesas_rateio', '07/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vAgo = NumberToString(CellGetN('base_despesas_rateio', '08/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vSet = NumberToString(CellGetN('base_despesas_rateio', '09/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vOut = NumberToString(CellGetN('base_despesas_rateio', '10/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vNov = NumberToString(CellGetN('base_despesas_rateio', '11/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vDez = NumberToString(CellGetN('base_despesas_rateio', '12/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));


ASCIIOutPut ('\\bbs2tm1qa01\tm1$\BBS_Rateio_Despesas_ORC\sourcefiles\export.csv', vArea, vTipoDespesa, vMetricas, vContaOrcamentaria, vJan, vFev, vMar, vAbr, vMai, vJun, vJul, vAgo, vSet, vOut, vNov, vDez);

My data source view has only "Total of Months" on period dimension. No leaf elements.

Here the result of the flat file for one data point to give the example about what is happening:

[img1][/img1]

I was expecting to bring me only the green row, where I have the months on columns. But all the yellow rows are duplicated and repeat the values for the account each month.

This is the problem. I have read the post and still don't know what to do.

lotsaram wrote: ↑Tue Jul 16, 2019 9:28 pm
Jorge Rachid wrote: ↑Tue Jul 16, 2019 8:43 pm I am using ASCIIOutPut and I want months on columns.

I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.

I didn't get what you have done about using flag and the total on view data source.

Can anyone explain that for me?
I could just go with Wim's earlier suggestion for someone else.
Wim Gielis wrote: ↑Mon Mar 27, 2017 6:15 am Did you really read the other contributions in this topic ?
Did you try them ?
But assuming you did actually read the other posts I will try and keep it very simple.

You have a TI process which you control. You can edit it!

- You need to change the data source to read only "Total Year". DON'T have all the months in the source view.
- Then have 12 x CellGetN on your data tab to read in the month values. Assign each to a separate variable
- Then in the AsciiOutput write out the 12 values. Ignore the Total Year value (or include it as a 13th column)
Code: Select all
sValJan = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Jan', 'Value' ) );
sValFeb = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Feb', 'Value' ) =;
...
sValDec = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Dec', 'Value' ) );

AsciiOutput( myFile, sValJan, sValFeb, ... sValDec );
Although now I actually did quickly scan the previous posts and I don't think I offered anything new or and simpler than what was said already (years ago).

Posted: **Tue Jul 16, 2019 10:41 pm**

Look at the Prolog tab and the view as the data source of the process.
You have a view defined on a cube with 9 dimensions.
Where in the csv output file do we see the dimensions that are not month, type, BU ?
Your AsciiOutput contains too little information (columns) and therefore if you have multiple records that appear to be the same, they in fact relate to other elements in the dimensions that you do not put in the file.

Posted: **Wed Jul 17, 2019 12:09 pm**

If you employ the method in this thread you will have to explicitly define subsets for every dimension in the cube you are using as the data source. Since you have to turn off "Skip Consolidated Values", if you don't specify a subset for a dimension you will get every element in the dimension in your view, including consolidated nodes, which can result in duplicate records (one record for the leaf and then x number of records for each parent it rolls to. Go back to your view definition and make sure you have appropriately defined subsets for each dimension. There is about a 99% chance this is your problem.

Posted: **Wed Jul 17, 2019 12:44 pm**

Hi Wim!
The cube in fact has 9 dimension, but on my flat file I only want only some of them.
But the other dimensions has only one element for each row of the flat file, so I do not think that is the problem, but I will a export with all dimension to see what happens.
Tks.

Wim Gielis wrote: ↑Tue Jul 16, 2019 10:41 pm Look at the Prolog tab and the view as the data source of the process.
You have a view defined on a cube with 9 dimensions.
Where in the csv output file do we see the dimensions that are not month, type, BU ?
Your AsciiOutput contains too little information (columns) and therefore if you have multiple records that appear to be the same, they in fact relate to other elements in the dimensions that you do not put in the file.

Posted: **Wed Jul 17, 2019 12:50 pm**

Hi tomok.
So to solve that I have to put only totals on my subsets on source view?
Thanks.

tomok wrote: ↑Wed Jul 17, 2019 12:09 pm If you employ the method in this thread you will have to explicitly define subsets for every dimension in the cube you are using as the data source. Since you have to turn off "Skip Consolidated Values", if you don't specify a subset for a dimension you will get every element in the dimension in your view, including consolidated nodes, which can result in duplicate records (one record for the leaf and then x number of records for each parent it rolls to. Go back to your view definition and make sure you have appropriately defined subsets for each dimension. There is about a 99% chance this is your problem.

Posted: **Wed Jul 17, 2019 1:01 pm**

Hi guys.
I have changed the source view and put all subsets that I don't want on flat file on total.
The other dimensions that I want I put all leaf elements.
On the source view am not skipping consolidated values.
Doing this it worked fine!
Thanks a lot for everyone that helped me.
Best regards.
JR

TM1 Forum

Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns

Re: Export Cube data in CSV file, with all Months as columns