DSXchange

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

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!

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

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.

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

What version are you on? 8.5 and higher offers looping in the Transformer stage.

we have V.8.1

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.

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.

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.

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.

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.

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

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?