TM1 Forum

I'm getting duplicate records in a TM1 extract after modifying the original code. The extract gives two numeric values from the conType dimension, Revenue and Volume. The original code did not extract records where Revenue was 0 but Volume was > 0. The new code does include those records but duplicates any records where both Revenue and Volume are > 0. For example, the original code gives these values:

Line 1) Revenue: 284,061.4; Volume: 95
Line 2) 43,223 0
Line 3) 2,583,056 38,000

The current code gives these values:
Line 1) Revenue: 284,061.4; Volume: 95
Line 2) 43,223 0
Line 3) 2,583,056 38,000
Line 4) Same as Line 1
Line 5) Same as Line 3

Here are the original and current versions of the relevant code in the prolog and data tabs:

Original Relevant Prolog Tab Code:
strType = 'Amount';
strDim = 'conType';
IF ( SUBSETEXISTS ( strDim, strSub ) = 1 );
SUBSETDELETEALLELEMENTS ( strDim, strSub );
ELSE;
SUBSETCREATE ( strDim, strSub );
ENDIF;
SUBSETELEMENTINSERT ( strDim, strSub, strType, 1 );
VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub );

Original Relevant Data Tab Code:
ASCIIOUTPUT ( strExportFile,
DIMENSIONELEMENTPRINCIPALNAME ( 'conCompany', vCo ),
strCoSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conPrimaryAcct', vPAcct ),
strPAcctSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conSubAcct', vSAcct ),
strSAcctSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conDetail', vDetail ),
strDetailSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conCostCenter', vCC ),
strCCSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conStateLoc', vStLoc ),
DIMENSIONELEMENTPRINCIPALNAME ( 'conVehicle', vVehicle ),

vYr | CELLGETS ( '}ElementAttributes_conMonth', vMo, 'MonthNum2Digit' ) | '01',

strFiscalPeriodSID,
NUMBERTOSTRING ( vValue ) ,
vScen );

Current Relevant Prolog Tab Code:
#strType = 'Amount';
strDim = 'conType';
strElement1 = 'Amount';
strElement2 = 'Volume';
IF ( SUBSETEXISTS ( strDim, strSub ) = 1 );
SUBSETDELETEALLELEMENTS ( strDim, strSub );
ELSE;
SUBSETCREATE ( strDim, strSub );
ENDIF;
#SUBSETELEMENTINSERT ( strDim, strSub, strType, 1 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement1, 1 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement2, 1 );
VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub );

Current Relevant Data Tab Code:
ASCIIOUTPUT ( strExportFile,
DIMENSIONELEMENTPRINCIPALNAME ( 'conCompany', vCo ),
strCoSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conPrimaryAcct', vPAcct ),
strPAcctSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conSubAcct', vSAcct ),
strSAcctSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conDetail', vDetail ),
strDetailSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conCostCenter', vCC ),
strCCSID,
DIMENSIONELEMENTPRINCIPALNAME ( 'conStateLoc', vStLoc ),
DIMENSIONELEMENTPRINCIPALNAME ( 'conVehicle', vVehicle ),

vYr | CELLGETS ( '}ElementAttributes_conMonth', vMo, 'MonthNum2Digit' ) | '01',

strFiscalPeriodSID,
# NUMBERTOSTRING ( vValue ) ,
NUMBERTOSTRING ( CELLGETN ( strCube, vScen, vCo, vPAcct, vSAcct, vDetail, vStLoc, vCC, vVehicle, 'Amount', strBook, vYr, vMo ) ),
NUMBERTOSTRING ( CELLGETN ( strCube, vScen, vCo, vPAcct, vSAcct, vDetail, vStLoc, vCC, vVehicle, 'Volume', strBook, vYr, vMo ) ),
vScen );

Any help would be greatly appreciated.

If you are outputting duplicates, I suspect it may be related to the View you have constructed. Can you run the process to build the View and then output the View itself/check it in the Cube to see whether you have i.e. added the same account and revenue elements multiple times.

I did check the view. The duplicates don't exist there. I had gotten a suggestion to turn the zero suppression off skip the records with zero values in both measure, but that ran too long. Thanks for the idea. Any other thoughts?

Try this.

Created a calculated measure showing 1 for the rows you want to include and 0 otherwise. Then do an extract on only that measure. Skip 0s but include rule calculated values. Then use CellGetN to get the revenue and volume numbers directly out of the cube for those rows.

Hi,

The Data tab is scanning over Amount and Volume separately. The reason it is duplicating is because each time the data tab runs, you are outputting two values (Amount and Volume) for the specified parameters, but you encounter those same params twice.

i.e.
Run 1 -> param1, param2 etc, Amount
Output 1 -> Amount, Volume
Run 2 -> param1, param2, Volume
Output 2 -> Amount, Volume