Trial balance and balance sheet amount validation

[parag.s.27]

Hi all,

I have to develop a mapping in which i have to generate a trial balance target file.

The source file is a flat file in which i'll be having an amount field which will be having amounts of all the months before a particular period. for e.g. if today is feb end date then source file will have amounts of Jan, and Feb. similarly if i want to run the mapping in dec 05 then source will have amounts from jan 05 to dec 05.

Now i am also having an associated date field called as period key which determines the period.

Now i have to device a logic such that if i am running a job in Feb end then the amount for feb should be difference of feb amount and previous month i.e. jan amount (Feb Amt - Jan Amt).

can any one suggest how to do this in a job using any stage or if there is any other mechanism

[kwwilliams]

Create stage variables to determine your previous month. Output that to a sort. Sort by account then month. Output to another xfm and then use stage variables set up like this:

CurrentAccount = link.account
balance = if (CurrentAccount = prevAccount) then link.Value = prevValue else 0
prevAccount = CurrentAccount
prevValue = link.Value

Put a constraint on your ouput to only output the CurrentMonth and you should then have a balance. That is pseudo code, I don't know your column names. link.Value represent that dollar values that you need to subtract to get a balance.

[kcshankar]

Hi Parag,

Now i have to device a logic such that if i am running a job in Feb end then the amount for feb should be difference of feb amount and previous month i.e. jan amount (Feb Amt - Jan Amt).

Is it always the difference between the current month and the previous month?
ie is this logic applicable only when you run the job on end of the month or on any other day of the month.


regards
kcs

[kumar_s]

Hi,

Code: Select all

if i want to run the mapping in dec 05 then source will have amounts from jan 05 to dec 05. 

Since your input file have the cummulative ammounts, is it you want to find the difference only for current month and previous month or current month and sum of all previous month?
What is your cardinality?
Let me assume you have to sum up based on account number to find the Trail Balance for that particular filed. And Each month has the lates informtaion, and hend you need to the difference between just two months.
If so, pass the current run date as the parameter, and filter out the data with month greater than month(Current_run_Date)-1.
Multiply with -1 if the month is one less than the current month. Sort based on the account number and month. Use aggregator to sum up the Amount based on the accunt number.

[parag.s.27]

Hi,

Code: Select all

if i want to run the mapping in dec 05 then source will have amounts from jan 05 to dec 05. 

Since your input file have the cummulative ammounts, is it you want to find the difference only for current month and previous month or current month and sum of all previous month?
What is your cardinality?
Let me assume you have to sum up based on account number to find the Trail Balance for that particular filed. And Each month has the lates informtaion, and hend you need to the difference between just two months.
If so, pass the current run date as the parameter, and filter out the data with month greater than month(Current_run_Date)-1.
Multiply with -1 if the month is one less than the current month. Sort based on the account number and month. Use aggregator to sum up the Amount based on the accunt number.

Yes ,
the ERP system that generates source files has a provision of placing data of each month in a seperate file. but those individual files have amount of cummulative addition. so if i find amount feb by substracting from original amt, the previous month then in similar fashion i can do it for all months