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

[raoulheinen]

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?

[OS_Pizza]

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]

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]

@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

[raoulheinen]

Thank you for the quick response. It is not completely fixed yet.

Hopefully above screenshot explains a bit more. Line 3 and 4 are part of the source file that we load, only two lines for now.

Line 9,10 and 11 should then be created after transformation. One line should then be created from the derivative rule as there is an F40 in column Segment6. This should be line10 in the screenshot. Line 9 is already has a sign change based on the account nr. and transformation rule for account which starts with a 2. Line 11 does not have a sign change as this starts with a 1, also coming from transformation rules for accounts.

Line 10 which should be created from the derivative should then be duplicated but with a sign change as this is an account starting with 2.

If i make the changes you proposed I get below from only the two files that i load.

[OS_Pizza]

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