8 Replies Latest reply: Jul 13, 2012 5:14 PM by HFM_Dev RSS

    FDM mapping rules

    HFM_Dev
      Hi Gurus,

      I have a scenario, where my source system is giving me a trial balance. I have an account #100. When this account#100 accounts gets balanced, in hfm it should be mapped to account#200 if the sign of balance as positive(+) and if the sign of the balance is negative(-) when it should be mapped to Account#300. By incoming source has balance against 100.And this 100 should be mapped account#200 or Account#300 based on sing of the amount. How do you establish these kind of rules in FDM?

      +100--------------------->Map Account#200
      -100--------------------->Map Account#300

      how to map based on sign of the amount(positive/negative)?
        • 1. Re: FDM mapping rules
          Raul Rodriguez
          Create a new mapping
          Source Account ->BridgeAcct1 Mapped to HFM Accoount 200
          Source Account ->BridgeAcct2 Mapped to HFM Accoount 100

          1. Create a DataPump Script
          2. Evaluate amount > or < 0
          3. Assign to Bridge Acct based on result

          Voila
          • 2. Re: FDM mapping rules
            674165
            sorry double post

            Edited by: thanasisa on May 6, 2012 6:26 PM
            • 3. Re: FDM mapping rules
              674165
              If I understood your question correctly...

              1. Use the mapping type like
              2. In the Rule value write the label of the source account e.g 100
              3. In the Target account write "#SCRIPT"
              4.In the script write

              If varValues(9) <0Then
              Result = “300”
              Else
              Result = “200”
              End If


              It should work. For more info check the fdm_admin.pdf

              Good luck
              • 4. Re: FDM mapping rules
                HFM_Dev
                Hi,

                When am trying to execute the above script i am getting the below error.

                <font color="Red">
                Error: 13- Type mismatch: 'VarValues' </font>

                Please help me..

                again My requirement is based on the sign of the value it should be mapped to different accounts

                Example:

                if the value is +100(Positive) it should be mapped to Account#200
                else the Value -100(Negative) it should be mapped to Account#300

                And Please explain me how to create the bridge account FDM?
                Regards
                Dev
                • 5. Re: FDM mapping rules
                  674165
                  Hi,

                  The mismatch means that it considers the VarValue as text and not a number.

                  For this reason you have to change the script.

                  First identify if your file considers the negative values with a minus before or after the number.

                  If the minus is before the number use the script:

                  If left(varValues(9),1) = "-" then
                  Result = “300”
                  Else
                  Result = “200”
                  End If


                  if the minus is after the number use the script:

                  If right(varValues(9),1) = "-" then
                  Result = “300”
                  Else
                  Result = “200”
                  End If

                  Good luck
                  • 6. Re: FDM mapping rules
                    HFM_Dev
                    Hi thanks for your help. but still Am not able to load the data using this Script.

                    Below is my txt.data loading file for Example:
                    <font color="Blue">
                    India,100(Acct),[ICP None],NOCC,NO Cust,None,None,-50000
                    </font>
                    I have written the below script still am not able to load the data.

                    If left(varValues(9),1) = "-" then
                    Result = “300”
                    Else
                    Result = “200”
                    End If

                    Please share me a sample data loading file and documentation for this requirement to my email devaraj9000@gmail.com

                    regards
                    Dev

                    Edited by: Dev on Jun 1, 2012 6:26 AM
                    • 7. Re: FDM mapping rules
                      869194
                      within the mapping table script, why don't the 'left' and 'mid' functions work? any workaround for this?

                      Edited by: 866191 on Jun 26, 2012 7:52 AM
                      • 8. Re: FDM mapping rules
                        HFM_Dev
                        Hi all

                        Thanks for your help.

                        Now its working fine.

                        i changed the script:

                        If varValues(9) <0 Then
                        Result = 100
                        Else
                        Result = 200
                        End If

                        regards
                        Dev