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?