Hi all,
I would like to have my csv to be targeted into 2 amount(the source file is one), please see my screencapture below, the red text combination(ACCOUNT and UD1) is trigger point for the duplication.
e.g: if account =710xxxxxx and UD1 =710xxxxxx Then
_Take the Amount from column 10 mapped it to amount_ _column_ and _take the Currency Code from column 9 and map it as cEUR_
end if
This is my import format:
I have seen that this has been in so many topics before,
Intragroups or how to load 2 data field into one amount field and Importing multiple amount columns from a single text file
But not sure if I can apply it correctly so I took the existing working VBscript that already exists in my current FDM and try to modify it as below, but it keeps giving grey fish, so my question:
1. can anyone help what is missing from my script???
2. I am open to any simpler way to achieve this, I tried with Logic group before---but can logic group take an amount scripting split? I cannot find any "amount" dimension in Complex Logic Group.
Function AmountTC(strField, strRecord)
'------------------------------------------------------------------
'Oracle Hyperion FDM DataPump Import Script:
'
'Created By: admin
'Date Created: 4/10/2017 2:12:52 PM
'
'Purpose:
'
'------------------------------------------------------------------
Dim cnSS 'ADODB.Connection
Dim SQL_HFM 'HFM DB string for Account Type
Dim rssql_HFM 'HFM DB record set
Dim rsAppend
Dim strWorkTableName
Dim AmountVal(1)
Dim SQL_Entity
Dim rssql_Entity
RES.PblnUseCol = True
Set RES.PColRecs = DW.DBTools.fCreateRecCol
RES.PColRecs.PblnAdd = True
RES.PColRecs.PblnUpdate = False
Set re = New RegExp
re.Pattern = " +"
re.Global = True
tmpRecord = re.Replace(strRecord," ")
LocationKey = RES.PlngLocKey
'******** Initialize objects ********
Set cnSS = CreateObject("ADODB.Connection")
Set rssql_HFM = CreateObject("ADODB.Recordset")
'******** Connect to ORACLE Database Server ********
cnss.open "Provider=OraOLEDB.Oracle.1;Data Source=ora2.abcd.com:1521/hfmeu;Database=EPM_HFM;User Id=EPM_HFM;Password=abcdef;"
'******** Find Entity ********
SQL_Entity = "Select DISTINCT PARTNAME From TPOVPARTITION WHERE PartitionKey = " & LocationKey
Set rssql_Entity = DW.DataAccess.farsFireHose(SQL_Entity)
If Not rssql_Entity.BOF And Not rssql_Entity.EOF Then
Entity = rssql_Entity.Fields("PARTNAME").Value
End If
'******** Find Account and UD1 ********
SCH_Account = Trim(DW.Utilities.fParseString(tmpRecord,12,4,";"))
SCH_UD1=Trim(DW.Utilities.fParseString(tmpRecord,12,7,";"))
'******** Getting the amount ***************************
AmountVal(1)=DW.Utilities.fParseString(tmpRecord,12,10,";")
If (IsNumeric(AmountVal(1)) and SCH_UD1 = "710610600" and SCH_Account="710-561011-703") or
(IsNumeric(AmountVal(1)) and SCH\_Account="710-112021-703") Then
If AmountVal(1) <> 0 Then
Amount=AmountVal(1)
Set NewRec = DW.DBTools.fCreateRec()
With NewRec
.colFlds.Add "PartitionKey", RES.PlngLocKey
.colFlds.Add "CatKey", RES.PlngCatKey
.colFlds.Add "PeriodKey", RES.PdtePerKey
.colFlds.Add "DataView", "YTD"
.colFlds.Add "Entity", "20854"
.colFlds.Add "Account", SCH\_Account
.colFlds.Add "Desc1", DW.Utilities.fParseString(tmpRecord,12,5,";")
.colFlds.Add "ICP", ICP
.colFlds.Add "UD1", "NoMove\_T"
.colFlds.Add "UD2", "c" & DW.Utilities.fParseString(tmpRecord,12,9,";")
.colFlds.Add "UD3", "\[None\]"
.colFlds.Add "UD4", "\[None\]"
.colFlds.Add "Amount",Amount
End With
RES.PColRecs.Append NewRec
End If
End If
End Function