04-13-2023
02:42 AM
- last edited on
07-31-2023
08:30 AM
by
JackLacava
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?
04-13-2023 09:32 AM
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.
04-13-2023 10:24 AM
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.
04-13-2023 10:45 AM
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
04-13-2023 12:02 PM
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.
04-14-2023 05:48 AM
Since line 4 in the source file, doesn't have any text value in segment6, Why is line 11 generated with F99_input?