on 04-08-2014 11:43 PM
Hi,
I have a table with Date,Monetory_Amount etc.. as columns and Another table (JRNL_Header) with columns Accounting_Period, Fiscal Year etc..
I need to calculate balance and Turnover from source data and load into target.
I asked my lead for a clue and got reply as below.
We have to get Year-To-Date balance for Income statement and Balance sheet accounts. You need to identify "Period Zero Record" from JRNL_Header table and aggregate all periodic activity.
For e.g. if you are planning to get data for Period 201409 then you need to first identify period zero balance and then add periodic activity for Period 1 to Period 9.
Period 9 Ending Balance = Sum(Period 0 balance) + sum(Period 1 activity) + sum(Period 2 activity) + sum(Period 3 activity) + .....................+ sum(Period 9 activity)
I created query transform for the balance field and mapping SUM(MONETORY_AMOUNT). I am joining the above two tables And in where clause, FiscalYear=2014, Accounting_Period>=0 and Accounting_Period<=09.
So from the above query transform, I am calculating SUM of Monetory_Amount for the year 2014 and for the Accounting periods 0 to 9.
But i was told that i have to use custom function for this. I am not very familiar with custom functions.
Please help me how to achieve this.
Can you share some sample records (Input) and how you want in the output. This will give more clarify as how exactly you want to calcluate.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
So you have to calculate sum(monetary amount) for accounting periods 0 to 9 correct?
Use a case statement and split the incoming records into 9 different output for each accounting period.
Then calculate the sum(monetary amount) for each of the 9 different accounting period and use a merge transform to do Union all.
Does that help?
Arun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.