reconcile debit and credit
Moderators: chulett, rschirm, roy
reconcile debit and credit
Need to apply logic in datastage to get bekow result
Create duplicate CREDIT records to adjustments amount TO old DEBIT DUE_DATE and as showen in below result.
The amount total for ADJUSTED_AMOUNT will be same as input
MATCH KEY:CUSTOMER_NUM,PART_NUM (between CREDIT and DEBIT RECORDS)
Input:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT
CREDIT |10001 |10 |20120301 |-100
DEBIT |10001 |10 |20120101 |50
DEBIT |10001 |10 |20120201 |100
CREDIT |10001 |10 |20120302 |-150
DEBIT |10001 |10 |20120202 |1000
CREDIT |10001 |10 |20120303 |-200
TOTAL 700
RESULT:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT |ADJUSTED_CREDIT_DATE |ADJUSTED_AMOUNT
CREDIT |10001 |10 |20120301 |100 |20120101 |-50
CREDIT |10001 |10 |20120301 | |20120201 |-50
CREDIT |10001 |10 |20120302 |150 |20120201 |-50
CREDIT |10001 |10 |20120302 | |20120202 |-100
CREDIT |10001 |10 |20120303 |200 |20120202 |-200
DEBIT |10001 |10 |20120101 |50 |20120101 |50
DEBIT |10001 |10 |20120201 |100 |20120201 |100
DEBIT |10001 |10 |20120202 |1000 |20120202 |1000
TOTAL 700
Thank you
Ashok
Create duplicate CREDIT records to adjustments amount TO old DEBIT DUE_DATE and as showen in below result.
The amount total for ADJUSTED_AMOUNT will be same as input
MATCH KEY:CUSTOMER_NUM,PART_NUM (between CREDIT and DEBIT RECORDS)
Input:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT
CREDIT |10001 |10 |20120301 |-100
DEBIT |10001 |10 |20120101 |50
DEBIT |10001 |10 |20120201 |100
CREDIT |10001 |10 |20120302 |-150
DEBIT |10001 |10 |20120202 |1000
CREDIT |10001 |10 |20120303 |-200
TOTAL 700
RESULT:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT |ADJUSTED_CREDIT_DATE |ADJUSTED_AMOUNT
CREDIT |10001 |10 |20120301 |100 |20120101 |-50
CREDIT |10001 |10 |20120301 | |20120201 |-50
CREDIT |10001 |10 |20120302 |150 |20120201 |-50
CREDIT |10001 |10 |20120302 | |20120202 |-100
CREDIT |10001 |10 |20120303 |200 |20120202 |-200
DEBIT |10001 |10 |20120101 |50 |20120101 |50
DEBIT |10001 |10 |20120201 |100 |20120201 |100
DEBIT |10001 |10 |20120202 |1000 |20120202 |1000
TOTAL 700
Thank you
Ashok
Last edited by dsqspro on Sat Feb 25, 2012 4:01 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Please write out the required logic in English. In that explanation, show us how six rows in come to be eight rows out, and under what circumstances that will occur. This is called a "specification". Writing out the logic may even allow YOU to solve the DataStage logic!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: request for solution
Hope it helps to understand my problem
1) Input file has debit and credit records
2) separate debit and credit records
3) sort on DUE_DATE descinding for debit and credit records on CUSTOMER_NUM , PART_NUM and DUE_DATE
4) match credit and debit on CUSTOMER_NUM and PART_NUM, for all matched groups apply oldest debit DUE_DATE
to the oldest credit as shown in the example and put in ADJUSTED_CREDIT_DATE.
5) Apply the debit amount to credit and place it in ADJUSTED_AMOUNT , if credit amount is more then create new duplicate
credit record and adjust with next debit amount.
6) Apply above logic until all credit get ADJUSTED_CREDIT_DATE in the group.
I know how to match credit and debit by CUSTOMER_NUM , PART_NUM.
My trouble is how to distribute debit amount from ingle debit record or the opposite way and in the end the TOTAL for ADJUSTED_AMOUNT should be equal to ACTUAL_AMOUNT
input records:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT
CREDIT |10001 |10 |20120301 |-100
DEBIT |10001 |10 |20120101 |50
DEBIT |10001 |10 |20120201 |100
result:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT |ADJUSTED_CREDIT_DATE |ADJUSTED_AMOUNT
CREDIT |10001 |10 |20120301 |100 |20120101 |-50
CREDIT |10001 |10 |20120301 | |20120201 |-50
DEBIT |10001 |10 |20120101 |50 |20120101 |50
DEBIT |10001 |10 |20120201 |100 |20120201 |100
1) Input file has debit and credit records
2) separate debit and credit records
3) sort on DUE_DATE descinding for debit and credit records on CUSTOMER_NUM , PART_NUM and DUE_DATE
4) match credit and debit on CUSTOMER_NUM and PART_NUM, for all matched groups apply oldest debit DUE_DATE
to the oldest credit as shown in the example and put in ADJUSTED_CREDIT_DATE.
5) Apply the debit amount to credit and place it in ADJUSTED_AMOUNT , if credit amount is more then create new duplicate
credit record and adjust with next debit amount.
6) Apply above logic until all credit get ADJUSTED_CREDIT_DATE in the group.
I know how to match credit and debit by CUSTOMER_NUM , PART_NUM.
My trouble is how to distribute debit amount from ingle debit record or the opposite way and in the end the TOTAL for ADJUSTED_AMOUNT should be equal to ACTUAL_AMOUNT
input records:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT
CREDIT |10001 |10 |20120301 |-100
DEBIT |10001 |10 |20120101 |50
DEBIT |10001 |10 |20120201 |100
result:
RECORD_TYPE |CUSTOMER_NUM |PART_NUM |DUE_DATE |ACTUAL_AMOUNT |ADJUSTED_CREDIT_DATE |ADJUSTED_AMOUNT
CREDIT |10001 |10 |20120301 |100 |20120101 |-50
CREDIT |10001 |10 |20120301 | |20120201 |-50
DEBIT |10001 |10 |20120101 |50 |20120101 |50
DEBIT |10001 |10 |20120201 |100 |20120201 |100
Thank you
dsqspro
dsqspro
Ray gives such good advice! Your first post compared to the second is like night and day.
What is the name of this specification? Is it how to reconcile debit and credit? Edit your topic's subject to help others who read the topic, because most any topic on this site can be labelled as a "request for solution."
When I see "request for ...," then I expect the winning bidder to receive a payment award. A request for solution should be worth more than a request for information, request for proposal, request for quote...
I'm going to ponder your question now because it really is quite interesting.
What is the name of this specification? Is it how to reconcile debit and credit? Edit your topic's subject to help others who read the topic, because most any topic on this site can be labelled as a "request for solution."
When I see "request for ...," then I expect the winning bidder to receive a payment award. A request for solution should be worth more than a request for information, request for proposal, request for quote...
I'm going to ponder your question now because it really is quite interesting.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Reconcile debit and credit records
I agree "reconcile debit and credit" should be topic heading and think if we can put credit record in loop unit the credit amount is filled with debit amount
and hold on all DUE_DATE's of debit records and transfer residue positive amount from each credit loop to
next credit record and it should carry until all it amount is filled.
Need help on how to put loop and try
and hold on all DUE_DATE's of debit records and transfer residue positive amount from each credit loop to
next credit record and it should carry until all it amount is filled.
Need help on how to put loop and try
Thank you
dsqspro
dsqspro
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Splitting credit and debit can be done in a Transformer stage, and you could take an additional output on each to an Aggregator stage to calculate the minimum (oldest) debit and credit, dumping those results into hashed files. That should be enough to join everything back together, and use a final Transformer stage to calculate the adjusted credit date.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Reconcile debit and credit records
Then please, by all means, go back to that first post and edit it to reflect this subject.dsqspro wrote:I agree "reconcile debit and credit" should be topic heading
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Version 8.7 server Transformer stage does not have looping (I just checked). On that basis I'll assert that version 8.5 server Transformer stage does not have looping either.chulett wrote:Out of curiousity, is that true for Server jobs? For some reason I though just Parallel ones supported that.qt_ky wrote:8.5 and higher offers looping in the Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
That's right, no looping feature in Server job Transformer stage, not even in 8.7. I'll have to remember that it's only new to parallel Transformer stages...
I would venture to say that it's so easy to create loops in Server routines that can be called from Server job Transformer stages, I don't see any big need to add a looping feature into the Transformer stage itself. Just create a Server routine with looping in BASIC. We're probably not going to see a lot of new features added in Server jobs.
I would venture to say that it's so easy to create loops in Server routines that can be called from Server job Transformer stages, I don't see any big need to add a looping feature into the Transformer stage itself. Just create a Server routine with looping in BASIC. We're probably not going to see a lot of new features added in Server jobs.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Re: request for solution
Can you explain a bit more about the above points with an example?dsqspro wrote:5) Apply the debit amount to credit and place it in ADJUSTED_AMOUNT , if credit amount is more then create new duplicate credit record and adjust with next debit amount.
6) Apply above logic until all credit get ADJUSTED_CREDIT_DATE in the group.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!