reconcile debit and credit

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
dsqspro
Premium Member
Premium Member
Posts: 20
Joined: Wed Apr 15, 2009 7:01 am

reconcile debit and credit

Post by dsqspro »

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
Last edited by dsqspro on Sat Feb 25, 2012 4:01 pm, edited 1 time in total.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
dsqspro
Premium Member
Premium Member
Posts: 20
Joined: Wed Apr 15, 2009 7:01 am

Re: request for solution

Post by dsqspro »

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
Thank you
dsqspro
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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." :idea:

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
dsqspro
Premium Member
Premium Member
Posts: 20
Joined: Wed Apr 15, 2009 7:01 am

Reconcile debit and credit records

Post by dsqspro »

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
Thank you
dsqspro
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

What version are you on? 8.5 and higher offers looping in the Transformer stage.
Choose a job you love, and you will never have to work a day in your life. - Confucius
dsqspro
Premium Member
Premium Member
Posts: 20
Joined: Wed Apr 15, 2009 7:01 am

Post by dsqspro »

we have V.8.1
Thank you
dsqspro
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

qt_ky wrote:8.5 and higher offers looping in the Transformer stage.
Out of curiousity, is that true for Server jobs? For some reason I though just Parallel ones supported that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Reconcile debit and credit records

Post by chulett »

dsqspro wrote:I agree "reconcile debit and credit" should be topic heading
Then please, by all means, go back to that first post and edit it to reflect this subject.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

chulett wrote:
qt_ky wrote:8.5 and higher offers looping in the Transformer stage.
Out of curiousity, is that true for Server jobs? For some reason I though just Parallel ones supported that.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

True... between the capabilities of BASIC routines and leveraging hashed files, you should be able to handle whatever needs handling.
-craig

"You can never have too many knives" -- Logan Nine Fingers
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Re: request for solution

Post by kandyshandy »

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.
Can you explain a bit more about the above points with an example?
Kandy
_________________
Try and Try again…You will succeed atlast!!
Post Reply