Forum Discussion

PieterDR's avatar
PieterDR
New Contributor II
2 years ago
Solved

Sum two columns in a datasource

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!

Import
Transformation
  • ChristianW's avatar
    ChristianW
    2 years ago

    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.

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    2 years ago

    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's avatar
      PieterDR
      New Contributor II
      2 years ago

      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's avatar
    CuroLewis
    New Contributor
    2 years ago

    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's avatar
      PieterDR
      New Contributor II
      2 years ago

      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's avatar
        ChristianW
        Valued Contributor
        2 years ago

        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.