Forum Stats

  • 3,759,060 Users
  • 2,251,495 Discussions
  • 7,870,477 Comments

Discussions

FDM 1 source with 2 target and 2 amount

User_3JG8T
User_3JG8T Member Posts: 198 Red Ribbon
edited Apr 11, 2017 9:30AM in Financial Data Management

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

pastedImage_2.png

This is my import format:

pastedImage_4.png

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

Tagged:

Answers

  • Dayalan Punniyamoorthy
    Dayalan Punniyamoorthy Member Posts: 1,516 Gold Trophy
    edited Apr 11, 2017 6:23AM

    I am not sure about the requirement, if 2 different users is going to run the data load rule using the CSV. Then its worth while to create 2 different locations (with respective import fromat,mapping & data load rule).

    In the mapping you manage how the data needs to be loaded.

    Note: Again you are the best to decide to go with fixing your Script or creating 2 different locations.

    Regards,

    Dayalan P.

  • User_3JG8T
    User_3JG8T Member Posts: 198 Red Ribbon
    edited Apr 11, 2017 9:30AM

    Hi Dayalan,

    Technically, I understand what you mean.

    But accounting process wise, that does not make sense, because based on accounting process, they would like to have the transaction currency to be uploaded at the same time with the functional currency.

    If there is two import format, there are possibilities where user are actually loading transactional currency, change the source data , and loading functional currency afterwards. Hence, the data will be incorrect. So best is to have scripts.

    tx.

This discussion has been closed.