DSXchange

Posted: **Mon Oct 27, 2014 7:15 am**

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

Posted: **Mon Oct 27, 2014 7:16 am**

Sort by key then by CRDR. Use an Aggregator stage to count groups. Don't forget to partition by key.

Posted: **Mon Oct 27, 2014 7:31 am**

So... a continuation / offshoot of this post it would seem.

Posted: **Tue Oct 28, 2014 5:01 am**

I want to find the maxmium (count) of consecutive C ro D for each account?

Posted: **Tue Oct 28, 2014 7:00 am**

Use a second Aggregator to find those maxima.

Posted: **Wed Oct 29, 2014 12:00 am**

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

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

Expected Output

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)

Hope this is clear.

How this can be achieved ? Thanks in Advance

Posted: **Thu Oct 30, 2014 3:43 am**

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

DSXchange

How to put this logic to Datastage Transformer