This content has been marked as final. Show 10 replies
#1 = You cannot use API items in Import scripts
#2 = I am not 100% sure I understand what you are doing here, could you provide some sample data? There's probably another way to accomplish this.
Thank you for your response. Our cost centers fall into three categories "C" "A" or "S" and the account mappings need to take these categories into consideration. Instead of concatenating the full cost center onto the account and mapping every combination I am hoping to append the category onto the account string. Below is some sample data of what i am hoping will occur.
Original Data File:
Account, CostCenter, amount
123, C998, $10
123, C778, $15
232, C656, $20
Data File after Script
Account, CostCenter, Amount
123_C, C998, $10
123_S, C778, $15
232_C, C656, $20
Please let me know if there is a better way to accomplish this. For user adoption purposes i would like to avoid conditional mapping if possible.
Thank you again for you guidance.
and I assume there are more than 3 UD5 map records? :) It's also too bad the cost center type isn't just buried into the account with a unique ID character .......
You should able to use :
To execute your own SQL statement against the DataMap table to get at the information .....
Thanks, yeah, there are about 3000 cost centers and the "category" is only tracked offline in a spread sheet...making this a little more fun that it otherwise needs to be :) Thanks again for your help. I'll take a stab and that approach.
I am running into the following error when i try the script below "Row cannot be located for updating. Some values may have been changed since it was last read" Please help. My desired end result is to concatenate the cost center type with the current account source string.
'Hyperion FDM EVENT Script:
'Date Created: 7/2/2012 3:46: PM
If LCase(strEventName) = "postworktomainprocess" Then
' Get work table
strWork = RES.PstrWorkTable
'Get the current location's profile
lngLocID = .lngLocKey
'Location equals SAP_TB
If .lngLocKey ="800" Then
If RunCCRules(strWork) <> False Then
RES.PlngActionType = 2
RES.PstrActionValue = RES.PstrActionValue & "<big>Updated for missing cost center;</big>"
strSQL = "SELECT " & strWork & ".AccountX, tCostCenterType.HFM_CostCenterType, tCostCenterType.HFM_CostCenter FROM " & strWork & _
" LEFT JOIN tCostCenterType ON " & strWork & ".UD1X = tCostCenterType.HFM_CostCenter" & _
" WHERE ((" & strWork & ".PartitionKey = '" & RES.PlngLocKey & "' AND " & _
strWork & ".PeriodKey = '" & RES.PdtePerKey & "' AND " & _
strWork & ".CatKey = '" & RES.PlngCatKey & "'))"
'Get recordset matching account criteria
Set rsSQL = API.DataWindow.DataAccess.farsKeySet(strSQL)
If Not .EOF And Not .Bof Then
Do While Not .Eof
strCostCenter = Trim(.Fields("HFM_CostCenterType").Value)
strCurrAccount = Trim(.Fields("AccountX").Value)
If .Fields("HFM_CostCenterType").Value <> "" Then
.Fields("AccountX").Value = strCurrAccount & StrCostCenter
Else .Fields("AccountX").Value = strCurrAccount
'Destroy the recordset object
Set rsSQL = Nothing
Edited by: user8714121 on Jul 2, 2012 4:39 PM
Somethings about your code:
#1 - I think what you're trying to do is update ALL of the records at once, which would be most efficient?
#2 - I do not see anything that is creating the table 'tCostCenterType' ?
#3 - The update may fail because your query is joining two tables?? Not 100% sure if that would work, especially since I can't figure out where the one table comes from
I might be over simplifying below, but I think that is what you want.
NOTE : Performance could be an issue with what I did below due to the querying against datamap table, though you could just do one select distinct to get all UD5 Src / Targ combinations, load it to a local array, and perform a search in the script.
Edited by: beyerch2 on Jul 2, 2012 5:33 PM
Function CCRule(strField, strRecord) 'Declare working variables Dim strCC Dim strSQL Dim rsSQL Dim strUD5Targ 'Assign values strCC = (DW.Utilities.fParseString(strRecord,8,3,",")) if trim(strCC) < > "" then 'Query Data Map table for given location ("partition"), Dimension, and SrcKey 'NOTE: This assumes EXPLICIT mapping is happening. If there is some type of 'LIKE' or 'BETWEEN' ' more thought required here. strSQL = "SELECT TargKey from tDataMap where PartitionKey = " & RES.PlngLocKey & " and DimName = 'UD5' and SrcKey = '" & strCC & "'" 'Get recordset matching account criteria set rsSQL = DW.DataAccess.farsFireHose(strSQL) 'Initialize Target UD5 strUD5Targ = "" 'First record in recordset should be result we want since we limited with WHERE clause in query 'NOTE: in ADO.NET we can do a ExecuteScalar call which only returns ONE result which would be perfect. 'Would limit overhead of recordset, etc, etc, etc....... 'NOTE : This *IS NOT* efficient for large amounts of imported rows as we're executing MANY queries here... ' If a lot of rows, perhaps a better idea is to read *ONCE* all of the distinct Src / Target UD5 values into an ' in-memory array and perform a search..... With rsSQL If Not .EOF And Not .Bof Then strUD5Targ = trim(.Fields("TargKey")) end if 'Release memory Set rsSQL = Nothing if strUD5Targ = "" then CCRule = strField else CCRule = strField & "_" & strUD5Targ else CCRule = strField end if End Function
Edited by: beyerch2 on Jul 2, 2012 5:34 PM
Edited by: beyerch2 on Jul 2, 2012 5:35 PM
Problem could be with this line
Else .Fields("AccountX").Value = strCurrAccount
try splitting over 2 lines
.Fields("AccountX").Value = strCurrAccount
The provided script worked like a charm. Thank you very much for your help with this!
Thank you for your help with this. This is SIGNIFICANTLY better than the path i was going down.
the only thing I would note is to watch the performance of that. There are ways to make it better if it 'sucks'.
Also, if it did answer your problem feel free to assign points. If I get to 1,000 points I get a free slinky or something. :-)