Forum Discussion

mpavan's avatar
mpavan
New Contributor II
2 years ago

Derive account based on Data Value in Transformation Rules.

Hello Experts, 

 

We have a requirement to derive account based on Data value. (If data value is Positive Asset Account , if negative Liability account)

This has to be done for range of accounts. I am not sure if there a any functions to refer data value for row? (GetSource and GetTarget seem to be only for dimension data)

Any ideas on how we can achieve this?

 

Thanks In Advance,

  • LeeB's avatar
    LeeB
    2 years ago

    One commonly used technique for value-based mapping is to bypass the original Account and create 2 Derivative Rules that prefix/append DR or CR depending on the value (use the Create If option).  You can then map the Accounts created by the Derivative Rule accordingly.

  • mpavan's avatar
    mpavan
    2 years ago

    Hello Lee, 

    Sorry for the delay.

    I had started to work on it but unfortunately account hierarchy is little messy and we do not have a defined range. Due to missing range Derivate rules are becoming complex. We went with the Event Handler rule that runs after transformation,

    For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
    Dim targetAccount As String = row(accountColIndex + StageConstants.TransformationColumnIncrements.Target)
    If targetAccount.Length > 0 Then
    Select Case targetAccount
    'Check for credit based accounts
    Case Is = "Trade Account"
    'We are on a credit account, flip the sign
    row.BeginEdit
    row(stageConstants.MasterKeyNames.FlipSign) = "True"
    Row.EndEdit
    End Select

    Thanks,

    Manoj

     

  • LeeB's avatar
    LeeB
    Contributor II
    2 years ago

    One commonly used technique for value-based mapping is to bypass the original Account and create 2 Derivative Rules that prefix/append DR or CR depending on the value (use the Create If option).  You can then map the Accounts created by the Derivative Rule accordingly.

    • mpavan's avatar
      mpavan
      New Contributor II
      2 years ago

      Hello Lee, 

      Sorry for the delay.

      I had started to work on it but unfortunately account hierarchy is little messy and we do not have a defined range. Due to missing range Derivate rules are becoming complex. We went with the Event Handler rule that runs after transformation,

      For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
      Dim targetAccount As String = row(accountColIndex + StageConstants.TransformationColumnIncrements.Target)
      If targetAccount.Length > 0 Then
      Select Case targetAccount
      'Check for credit based accounts
      Case Is = "Trade Account"
      'We are on a credit account, flip the sign
      row.BeginEdit
      row(stageConstants.MasterKeyNames.FlipSign) = "True"
      Row.EndEdit
      End Select

      Thanks,

      Manoj

       

    • Samai's avatar
      Samai
      New Contributor
      8 months ago

      Hi, I tried this in some variations now. First approach was 

      ... with Math Value = 0 (well it should be positive or negative). But then nothing happened. No additional lines were generated. Trying back an forth I finally managed to get the lines in stage with the following setting

      •  Create if < x with MathValue = 1
      • Create if > x with MathValue = -1

      Unfortunately this is overlapping, but also MathValue = 1 for both lines only generated the negative-accounts. Ok, not relevant for this case (Korean Wong) but not reliable for other currencies.

      However, is this behaviour by design or a bug?

      Thanks

      Michael