Skip to main content
Newcomer
Apr 13, 2023 6:42 AM UTC

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?

 

 

5 replies

Apr 13, 2023 1:32 PM UTC

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.

Newcomer
Apr 13, 2023 2:24 PM UTC

 

 

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.

2023-04-13 16_19_27-Window.png

Apr 13, 2023 2:45 PM UTC

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 -

OS_Pizza_0-1681397045956.png

 

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

Apr 14, 2023 9:48 AM UTC

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