Counting Records in TI

Post Reply
Economic
Posts: 7
Joined: Wed Sep 08, 2010 12:55 am
OLAP Product: TM1
Version: 9.5
Excel Version: Excel 2003 2007

Counting Records in TI

Post by Economic » Tue May 28, 2013 7:23 am

Hi All,

I was wondering if you could help me.
As I'm importing the data, I would like to count the records and assign a '1' to each record.

No Count Cumulative
0001AE 1 1
0002AE 1 2
0003AE 1 3

I need a TI formula so that as each new record is imported it assigns in the 'measures' dimension a '1'
I'm sure it is relatively simple but I am fairly new to all of this.

Regards,
Arthur

declanr
MVP
Posts: 1657
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: Counting Records in TI

Post by declanr » Tue May 28, 2013 7:31 am

Arthur,

In your prolog add the line:

Code: Select all

iCount = 1;
and then at the very end of your Data tab add the line:

Code: Select all

iCount = iCount + 1;
HTH
Declan Rodger

Economic
Posts: 7
Joined: Wed Sep 08, 2010 12:55 am
OLAP Product: TM1
Version: 9.5
Excel Version: Excel 2003 2007

Re: Counting Records in TI

Post by Economic » Wed May 29, 2013 5:11 am

Hi,

Thanks for the reply and appreciate your help.

I've put in the script as advised but how does it know to insert a 1 against each record in the measures dimension?

In other words, the intersection of 0001AE, Department, Year, Month insert a 1 for each unque record... Does this make sense..

I think it would be simple to do, but i'm simply stumped. I have alot to learn.

declanr
MVP
Posts: 1657
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: Counting Records in TI

Post by declanr » Wed May 29, 2013 5:31 am

Economic wrote:how does it know to insert a 1 against each record in the measures dimension?
It doesn't know how to do that at this point, the above only gives you a counter that increments with every subsequent data record.

In order to put a 1 and the counter against the measures you would need to use the CellPutN function:

Code: Select all

CellPutN ( 1, 'Cube', 'Dim1 Element', 'Dim2 Element',....., 'Count' );
CellPutN ( iCount, 'Cube', 'Dim1 Element', 'Dim2 Element',....., 'Cumulative'); 
Declan Rodger

Economic
Posts: 7
Joined: Wed Sep 08, 2010 12:55 am
OLAP Product: TM1
Version: 9.5
Excel Version: Excel 2003 2007

Re: Counting Records in TI

Post by Economic » Thu May 30, 2013 1:59 am

Hi,

Thank you for this. I thought I was on the right track, you clarified things for me.

I still have one more problem if you could help please./

When the 1 is populated against each record it seems to apply the 1 to the record before.

Eg 00001AE 0 when it should be 1. When I calculate the values for this item it shows the incorrect value. I have tried changing icount to icount=1 or icount=0, but this does not fix the issue.

Looking below the 4622 should be shown on Ref 18680

..........................Ref..........Count......Actual Weight (KG)
DHOSAUS EA..........18679......4622........0
DHOSAUS EA..........18680......0............3312[/img]Thanks again!

Regards,
Arthur

declanr
MVP
Posts: 1657
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: Counting Records in TI

Post by declanr » Thu May 30, 2013 5:46 pm

Economic wrote: When the 1 is populated against each record it seems to apply the 1 to the record before.
Arthur,

It just sounds like the order of your code is somewhat "askew" - If you could provide a few lines of how your datasource appears (obviously you can replace it with dummy data if there is anything confidential) and provide the actual script from all the tabs of your TI it would be easier to provide an answer.

Thanks,
Declan
Declan Rodger

Economic
Posts: 7
Joined: Wed Sep 08, 2010 12:55 am
OLAP Product: TM1
Version: 9.5
Excel Version: Excel 2003 2007

Re: Counting Records in TI

Post by Economic » Thu May 30, 2013 11:21 pm

Hi Declan,

There is a long list of generated statements of CellPutN, but left most of these out as I don't believe it is causing the problem.

In the prolog tab:

#****Begin: Generated Statements***
DIMENSIONSORTORDER('Shipment_Type','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('Client_Code','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('reps_cde','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('Inco_Terms','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('Carrier_No','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('orig_cde','','','BYINPUT','ASCENDING');
DIMENSIONSORTORDER('dest_cde','','','BYINPUT','ASCENDING');
OldCubeLogChanges = CUBEGETLOGCHANGES('au-customer_detail');
CUBESETLOGCHANGES('au-customer_detail', 0);
#****End: Generated Statements****

Cube01='au-customer_summary';
Cube02='au-customer_detail';

subname='zZeroActualsCustomer';

IF(VIEWEXISTS(Cube01, 'zZeroActuals')=1);
VIEWDESTROY(Cube01, 'zZeroActuals');
ENDIF;

IF(VIEWEXISTS(Cube02, 'zZeroActuals')=1);
VIEWDESTROY(Cube02, 'zZeroActuals');
ENDIF;

VIEWCREATE(Cube01, 'zZeroActuals');
VIEWCREATE(Cube02, 'zZeroActuals');

#Year
IF(SUBSETEXISTS('hwl_year', subname)=1);
SUBSETDESTROY('hwl_year', subname);
ENDIF;
SUBSETCREATE('hwl_year', subname);
SUBSETELEMENTINSERT('hwl_year', subname, pYear, 1);
VIEWSUBSETASSIGN(Cube01, 'zZeroActuals', 'hwl_year', SubName);
VIEWSUBSETASSIGN(Cube02, 'zZeroActuals', 'hwl_year', SubName);

#Month
IF(SUBSETEXISTS('hwl_month', subname)=1);
SUBSETDESTROY('hwl_month', subname);
ENDIF;
SUBSETCREATE('hwl_month', subname);
SUBSETELEMENTINSERT('hwl_month', subname, pMonth, 1);
VIEWSUBSETASSIGN(Cube01, 'zZeroActuals', 'hwl_month', SubName);
VIEWSUBSETASSIGN(Cube02, 'zZeroActuals', 'hwl_month', SubName);

VIEWZEROOUT(Cube01,'zZeroActuals');
VIEWZEROOUT(Cube02,'zZeroActuals');

iCount = 1;

In the Metadata Tab:

IF(SUBST(ShipperOrConsignee,2,1)@='');
ShipperOrConsignee = 'UNKNOWN';
ELSEIF(SUBST(ShipperOrConsignee,1,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,2,LONG(ShipperOrConsignee)-1);
ELSEIF(SUBST(ShipperOrConsignee,7,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,1,6);
ELSEIF(SUBST(ShipperOrConsignee,8,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,1,7);
ENDIF;

#****Begin: Generated Statements***
vShipmentNo=SUBST(ShipmentNo,3,5);
vBranchCode=SUBST(ShipmentNo,1,2);
vDepartmentCode=SUBST(ShipmentNo,8,2);
vCostCenter=CELLGETS('BrnDept_Lookup',vBranchCode|vDepartmentCode,'Cost Centre');
vOrigin=IF(SUBST(OriginCountry|OriginCity,1,1)@='','Unknown',OriginCountry|OriginCity);
vDestination=IF(SUBST(DestinationCountry|DestinationCity,1,1)@='','Unknown',DestinationCountry|DestinationCity);
vVersion='Actual';
vMonth=IF(LONG(ShipmentDate)=9,SUBST(ShipmentDate,3,2),SUBST(ShipmentDate,4,2));
vYear=IF(LONG(ShipmentDate)=9,SUBST(ShipmentDate,6,4),SUBST(ShipmentDate,7,4));
DIMENSIONELEMENTINSERT('Shipment_Type','',ShipmentType,'n');
DIMENSIONELEMENTINSERT('Client_Code','',ShipperOrConsignee,'n');
DIMENSIONELEMENTINSERT('reps_cde','',SalesRepName,'n');
DIMENSIONELEMENTINSERT('Inco_Terms','',IncoTerms,'n');
DIMENSIONELEMENTINSERT('Carrier_No','',CarrierNo,'n');
DIMENSIONELEMENTINSERT('orig_cde','',vOrigin,'n');
DIMENSIONELEMENTINSERT('dest_cde','',vDestination,'n');
#****End: Generated Statements****

In the Data Tab:

IF((SUBST(ShipmentNo,1,1)@='3')%(SUBST(ShipmentNo,1,1)@=''));
ITEMSKIP;
ENDIF;

IF(SUBST(ShipperOrConsignee,2,1)@='');
ShipperOrConsignee = 'UNKNOWN';
ELSEIF(SUBST(ShipperOrConsignee,1,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,2,LONG(ShipperOrConsignee)-1);
ELSEIF(SUBST(ShipperOrConsignee,7,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,1,6);
ELSEIF(SUBST(ShipperOrConsignee,8,1)@='-');
ShipperOrConsignee = SUBST(ShipperOrConsignee,1,7);
ENDIF;

iCount = 1;
CellPutN(1, 'au-customer_detail',vYear,vMonth,vVersion,vCostCenter,vShipmentNo,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Job / Shipment Count');
CellPutN(iCount, 'au-customer_detail',vYear,vMonth,vVersion,vCostCenter,vShipmentNo,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Job / Shipment Count');


#****Begin: Generated Statements***
vShipmentNo=SUBST(ShipmentNo,3,5);
vBranchCode=SUBST(ShipmentNo,1,2);
vDepartmentCode=SUBST(ShipmentNo,8,2);
vCostCenter=CELLGETS('BrnDept_Lookup',vBranchCode|vDepartmentCode,'Cost Centre');
vOrigin=IF(SUBST(OriginCountry|OriginCity,1,1)@='','Unknown',OriginCountry|OriginCity);
vDestination=IF(SUBST(DestinationCountry|DestinationCity,1,1)@='','Unknown',DestinationCountry|DestinationCity);
vVersion='Actual';
vMonth=IF(LONG(ShipmentDate)=9,SUBST(ShipmentDate,3,2),SUBST(ShipmentDate,4,2));
vYear=IF(LONG(ShipmentDate)=9,SUBST(ShipmentDate,6,4),SUBST(ShipmentDate,7,4));
CellPutN(CellGetN('au-customer_detail',vYear,vMonth,vVersion,vCostCenter,vShipmentNo,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Actual Weight (KG)')+ActualWeightKgs,'au-customer_detail',vYear,vMonth,vVersion,vCostCenter,vShipmentNo,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Actual Weight (KG)');
#****End: Generated Statements****



CellPutN(ActualWeightKgs,'au-customer_summary',vYear,vMonth,vVersion,vCostCenter,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Actual Weight (KG)');
CellPutN(ChargeableWeight,'au-customer_summary',vYear,vMonth,vVersion,vCostCenter,ShipmentType,ShipmentStat,ShipmentTerms,IncoTerms,MovementType,vOrigin,vDestination,ShipperOrConsignee,SalesRepName,CarrierNo,'Chargeable Weight (KG)');

Post Reply