TM1 Forum

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

Arthur,

In your prolog add the line: and then at the very end of your Data tab add the line: HTH

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.

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:

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

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

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)');