How to put this logic to Datastage Transformer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Initialize stage variables to 0. Detect change in key, possibly using LastRowInGroup() function. Accumulate while not last row in group; reset once last row in group has been processed. Use extra stage variables to "remember".
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks Ray for your support.
I am afraid that I couldn't make my requirement clear to you all.
I got transaction happend to accounts for one month. I need to find the maximum (count) of consecutive credits and debits per account. So my input data is as follows
Expected Output
Hope this is clear.
How this can be achieved ? Thanks in Advance
I am afraid that I couldn't make my requirement clear to you all.
I got transaction happend to accounts for one month. I need to find the maximum (count) of consecutive credits and debits per account. So my input data is as follows
Code: Select all
Row Seq Account Transaction Date Debit/Credit
01 A1 2014-09-01 D
02 A1 2014-09-01 C
03 A1 2014-09-02 C
04 A1 2014-09-03 C
05 A1 2014-09-04 D
06 A1 2014-09-04 D
07 A1 2014-09-04 D
08 A1 2014-09-12 D
09 A1 2014-09-14 C
10 A1 2014-09-16 C
11 A1 2014-09-28 D
12 A1 2014-09-30 D
13 A2 2014-09-01 C
14 A2 2014-09-01 D
15 A2 2014-09-06 D
16 A2 2014-09-13 C
17 A2 2014-09-14 C
18 A2 2014-09-14 C
19 A2 2014-09-24 C
20 A2 2014-09-29 D
21 A2 2014-09-29 C
22 A2 2014-09-29 C
23 A2 2014-09-26 D
24 A2 2014-09-26 C
25 A2 2014-09-27 C
26 A2 2014-09-27 D
27 A2 2014-09-28 D
28 A2 2014-09-30 D
29 A2 2014-09-30 D
Code: Select all
Account Debit/Credit Maximum Count of Consecutive Debit/Crebit
A1 C 3 ( continuously in credit are [rows (02,03,04), rows (09,10)] and max consecutive credit count - (rows 02, 03 and 04)=3)
A1 D 4 ( continuously in debit are [rows (01), rows(05,06,07,08), rows(11,12)] and max consecutive debit count - (rows 05, 06, 07 and 08)=4)
A2 C 4 ( continuously in credit are [rows (01), rows(16,17,18,19), rows(21,22), row(24), row(26)] and max consecutive credit count - (rows 16, 17, 18 and 19)=4)
A2 D 3 ( continuously in debit are [rows(14,15), rows(20), rows(23),rows(25),rows(27,28,29)] and max consecutive debit count -(rows 27, 28 and 29)=3)
How this can be achieved ? Thanks in Advance
JJ
I think, You can get the desired output by the below process.
1)Source Stage
2)Sort stage
Key=Account
Sort Key Mode=Don't sort(previously sorted)
Key=Debit/Credit
Create Cluster Key Change Column=Yes
3)Transformer:
Sv1=1
Sv2=If Cluster Key change column=1 then SV1 else Sv2+1
Take 2 constraints and 2 outputs 1 for Credit and 2nd for Debit.
4)Aggregator1 for 1st o/p link of Transformer.
Grouping on Account.
use max function.
Aggregator2 for 2nd o/p link of Transformer.
Grouping on Account.
use max function.
5)Funnel
Sort on Account and Debit/Credit
6)Target
You can get the desired output.
-------------------------------------
RPhani
1)Source Stage
2)Sort stage
Key=Account
Sort Key Mode=Don't sort(previously sorted)
Key=Debit/Credit
Create Cluster Key Change Column=Yes
3)Transformer:
Sv1=1
Sv2=If Cluster Key change column=1 then SV1 else Sv2+1
Take 2 constraints and 2 outputs 1 for Credit and 2nd for Debit.
4)Aggregator1 for 1st o/p link of Transformer.
Grouping on Account.
use max function.
Aggregator2 for 2nd o/p link of Transformer.
Grouping on Account.
use max function.
5)Funnel
Sort on Account and Debit/Credit
6)Target
You can get the desired output.
-------------------------------------
RPhani