I am fairly new to TM1 as a technology and since there are no other folks who are proficient in my org, reaching out here for your kind help/pointers please...
We have a TI process named "Employee Details_Division Transfer" which in its earlier form was used to transfer an employee in the EMS_EMPLOYEE DEtails Cube i.e. change their STATUS to 1 under the Target Division (ToDivision) and change their STATUS to 0 under the Source Division (FromDivision).
Business wanted to write in higher STATUS Values i.e. >1 in the EMS_Employee Details cube which is being done without any issues. However, now owing to higher status values the above TI Process code needs to be changed -- NEW GOAL is to enable the transfer of Higher Status from Source Division to Target division and make STatus as 0 under Source Division for a particular employee.
I have been trying to make Code Changes to achieve the above Goal. I have managed to retrieve and transfer the higher STATUS values as required above. However, for writing in the STATUS as 0 under the Source Division (FromDivision) -- I have failed multiple times.
No matter what employee I test for, the code of writing in 0 does not work properly. What is really happening is -- it is writing in 0 for all the records for that employee for both SourceDivision and TargetDivision.
Could you please help me in correcting the code for the above problem in the DATA Tab? I am completely stuck and really losing hope/confidence in myself as I am not able to decipher where it's going wrong.
I am describing an example for you to get a clear understanding and have attached screenshots and code snippets of my TI Process which I had created.
Example:
Here trying to transfer Employee 010018 from SourceDivison 5410 to TargetDivison 7207 for the effective month of May (i.e. TI process will operate on data from May till Dec).
The issue is occurring because of the last line of code with CellPutN in the DATA Tab. (5th point below)
Info and Attachments:
1. DataSource is the cube itself
2. Variables
Variable Name | Type | Contents
vScenario | String | Other
vYear | String | Other
vMonth | String | Other
vDivision | String | Other
vEmployee | String | Other
vEmployeeDetails | String | Other
vValue | String | Other
3. Parameters
Parameter | Type | Entered Value
pScenario | String | Current Forecast
pYear |String | 2021
pMonth |String | May
pEmployeeName |String | 010018
pFromDivision |String | 5410
pToDivision |String | 7207
pDelimiter | String | +
4. Prolog Code
Code: Select all
#****Prolog****#
#****Begin: Generated Statements***
#****End: Generated Statements****
#======================================================================
#Process for transfer data fromEmployee Details to Employee Details for Division Transfer
#======================================================================
cProcess = GetProcessName();
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.';
cViewName = '}' | cProcess | '.' | NumberToString( ROUND( RAND( ) *100000 ) );
cSubsetName = cViewName;
sDebugFile = cDebugFile | 'Prolog.log';
cCubeName = 'EMS_Employee Details';
#=================
#source view creation
#=================
If( ViewExists( cCubeName, cViewName ) = 1 );
ViewDestroy( cCubeName, cViewName );
EndIf;
ViewCreate( cCubeName, cViewName );
ViewExtractSkipCalcsSet( cCubeName, cViewName, 1 );
ViewExtractSkipZeroesSet( cCubeName, cViewName, 1 );
ViewExtractSkipRuleValuesSet( cCubeName, cViewName, 1 );
#=============================================
#Excluding the previous months from the selected month
#=============================================
vExclude='';
z = DIMSIZ('Month');
While(z>0);
vMonth= DIMNM('Month', z);
x=ELLEV('Month',vMonth);
IF(x=0);
IF(ATTRN('Month', vMonth, 'Month Number') < ATTRN('Month', pMonth, 'Month Number'));
vExclude=vExclude|vMonth|'&';
ENDIF;
ENDIF;
z = z - 1;
END;
vExcluded=SUBST(vExclude, 1,(Long(vExclude)-1));
#------------BEGIN Scenario------------
vDimName = 'Scenario';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pScenario, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------------END Scenario----------------
#------------BEGIN Year----------------
vDimName = 'Year';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pYear, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#----------END Year------------------
#---------BEGIN Employee-------
vDimName = 'Employee';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
ExecuteProcess('Bedrock.Dim.Sub.Create.ByElement',
# 'pDimension', vDimName, 'pSubset', cSubsetName, 'pElements', pEmployeeName
'pDimension', vDimName, 'pSubset', cSubsetName, 'pElements', pEmployeeName, 'pDelimiter',pDelimiter
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#-------------END Employee_____
#--------BEGIN Division-----
vDimName = 'Division';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pFromDivision, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------END Division____
#-----------BEGIN All N level elements for all other dimensions------------
vDimName = 'Employee Details';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', ''
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------------END All N level elements for all other dimensions----------
vDimName = 'Month';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', vExcluded
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#------------END All N level elements for all other dimensions----------
#====================
# Completed view creation
#====================
#===============
#Assign Datasource
#===============
DataSourceType = 'VIEW';
DatasourceNameForServer = cCubeName;
DatasourceNameForClient = cCubeName;
DatasourceCubeView = cViewName;
Code: Select all
#****Data Tab Code****#
#****Begin: Generated Statements***
#****End: Generated Statements****
#==============================================
#Code for transfering the employee details within the division
#==============================================
if (VALUE_IS_STRING=1, CellPutS(SVALUE,'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails),
CellPutN(NVALUE, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,vEmployeeDetails));
#=======================================================
#Retriving the status of Source month and Source Divison
#=======================================================
vStatus=CellGetN('EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');
#==============================================================================
#Writing the status of Successive month from the selected month till last month for the Target
#==============================================================================
CellPutN(vStatus, 'EMS_Employee Details',vScenario,vYear,vMonth,pToDivision,vEmployee,'Status');
#=======================================================
#================================================
#Making the Status of Source Month as 0 -- ISSUE is here
#================================================
CellPutN(0, 'EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status');
#======Trying to Debug the non-working code of above for Status being written to 0====================
vDebugPath='\\test\ems\data\Debug.csv';
IF(~((CellGetN('EMS_Employee Details',vScenario,vYear,vMonth,pFromDivision,vEmployee,'Status')) = 0));
ASCIIOUTPUT(vDebugPath,vScenario,vYear,vMonth,pFromDivision,vEmployee,vEmployeeDetails,vValue);
EndIF;
Code: Select all
Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****
#==========================
#BEGIN CLEAN VIEW/SUBSETS
#==========================
#cleanup/destroy view and subsets
ViewDestroy(cCubeName, cViewName);
i = 1;
WHILE (tabdim(cCubeName,i) @<> '');
SubsetDestroy(tabdim(cCubeName,i), cSubsetName);
i = i + 1;
END;
#==========================
#END CLEAN VIEW/SUBSETS
#=========================
7. Before Execution -- Employee Details cube
8. After Execution -- Employee Details cube (with keeping the problematic code for writing in STATUS as 0 commented)
9. After Execution -- Employee Details cube (executing the problematic code for writing in STATUS as 0)
Kindly help! I would be much grateful.
Thanks so much!
-Arden