Derive account based on Data Value in Transformation Rules.

[mpavan]

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 · Accepted solution]

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]

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]

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