Forum Discussion

raoulheinen's avatar
raoulheinen
New Contributor II
2 years ago

Derivative rule - Duplicate account and base amount (+ or -) on account on first digit of account

Hi community,

For a client I am looking for the following:

- Data extract from Yardi is loaded via .csv file into OneStream. 

- The data file contains columns for amount = beginning balance + amount ,account = account  and flow = Segment6 column.

FinTrialBalances                                                            
Transaction_Type Control_Number Blank_1 Blank_2 Transaction_Date Post_Month Reference Notes Property_Code Amount Account Blank_3 Blank_4 Books Remarks Blank_5 Beginning_Balance Blank_6 Blank_7 Segment1 Segment2 Segment3 Segment4 Segment5 Segment6 Segment7 Segment8 Segment9 Segment10 Segment11 Segment12
J 14202303     3-1-2023 23-Mar :TrialBal   xxx 0 22020100     1     -2866.67               F40 - Reclasses in Bal. Sheet            
J 14202303     3-1-2023 23-Mar :TrialBal   xxx 0 12020100     1     -50000               F40 - Reclasses in Bal. Sheet            

 

- I have created a derivative rule that is duplicating the lines that contain a value in the flow column.

- When in the file there is a text value in the segment 6 column it is duplicated into an extra line with flow F99_input.

 

What I cannot achieve is the following:

What I want to do is that based on the account column =  Account that when the account starts with a 2 then the amount should be multiplied by -1

When the account starts with a 1 then the amount should be as is.

The above file should therefore be after transformation contain 4 lines in which on duplicated line is multiplied by -1.

Does anyone have an idea on how I can fix this?

 

 

Data Integration
Import
Transformation
  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    Can you share a screenshot of the derivative rule that you have already created for the below scenario ?

    - When in the file there is a text value in the segment 6 column it is duplicated into an extra line with flow F99_input.

    Based on that i can recommend the addition rule that you can create.

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

       

       

      This is the derivative rule that is used as derivative (target) in the transformation rule. Whenever it finds an F40 then it duplicates the line with an F99_input. The amount although needs to be either positive or negative based on the account number.

      • OS_Pizza's avatar
        OS_Pizza
        Contributor III
        2 years ago

        raoulheinen 

        I suppose you dont have to create multiple derivative rule , that will add up extra maintenance whenever you have new segment6.

        Let me know if the below rule works. You just have to create these 2 rules -

         

        If it helped , Please give kudos and mark the answer as accepted solution

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    Since line 4 in the source file, doesn't have any text value in segment6, Why is line 11 generated with F99_input?