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
Counting Records in TI
-
- 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: Counting Records in TI
Arthur,
In your prolog add the line:
and then at the very end of your Data tab add the line:
HTH
In your prolog add the line:
Code: Select all
iCount = 1;
Code: Select all
iCount = iCount + 1;
Declan Rodger
-
- 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
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.
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.
-
- 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: Counting Records in TI
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.Economic wrote:how does it know to insert a 1 against each record in the measures dimension?
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
-
- 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
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
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
-
- 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: Counting Records in TI
Arthur,Economic wrote: When the 1 is populated against each record it seems to apply the 1 to the record before.
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
-
- 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
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)');
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)');