Sum two columns in a datasource

[PieterDR]

Hi all, 

In my delimited upload file (trail balance) there are two columns that include amounts: beginning balance and the sum of periodic movements. The sum of the two is the number I want uploaded in the import stage. 

Is there a way to make OneStream add an additional calculated column (source dimension) to the datasource with the sum of the two columns (so prior to transformation / validate / process)? 

Any help would be much appreciated!

[ChristianW]

You can use a matrix load setup for this (e.g. flow member PerBegBal and Periodic) and then sum it up with the transformation rule (e.g. PerBegBal -> None and Periodic -> None).

[PieterDR]

Thanks! That is basically what I am doing now in a separate dimension (both columns are set up as UD4Root and the UD4 dimension calculates the sum).

However I am also working with derivative (source) rules:
Input account X is OneStream account 1 if amount > 0, and is OneStream account 2 if amount < 0.

The current order of the upload to transformation first applies the derivative and than applies the transformation.

The result is that if beginning balance (BB) is <0, and periodic movements PM are >0, the beginning balance will be transformed to OneStream account 2, and the movements to account 1, effectively blowing up my balance sheet. 
I want OneStream to look at the total of BB and PM first, and then apply the derivative to that total.

[CuroLewis]

I think prior to transformation you'd use the data source member target Logical Operator which can be either None, a BR or Complex Expression. When you select either BR or Complex Expression your logic goes in the Logical Expression. 

[PieterDR]

Thanks, I was thinking towards that direction myself as well. I have very basic knowledge of VBA: any chance you could help me with the code?

[ChristianW · Accepted solution]

You will find samples in Snippets:

If snippets are not available with your installation, you can find them on the solution exchange. An administrator needs to install them for you and then they will be available on all applications.

Of cause, you need to summ the columns instead of concatenating them.

[PieterDR]

Thanks a lot! This has worked and helps a lot