Hi,
First should thank all the suggestion for my previous questions.
I have an alternate method of arriving at the no of rows used, but my question is : In an Aggregator stage, is there a way to count the no of rows?
e..g, if it's Grouped by A/C Nr and there are 10 records for this A/C No, I would like to know this Number [10]
Row Count in Aggregator
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
please read below message
Last edited by talk2shaanc on Wed Oct 25, 2006 1:13 pm, edited 1 time in total.
Shantanu Choudhary
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
I am not aware of your other design detail, so my suggestion would be based on some assumptions.
If ActNo is one of the key columns and is at the higher level, then it would be an additional benefit. Assuming it is; I would sort the data on ActNo and hash partition on ActNo and enable "Create Cluster key change column". In the transformer stage (that you may be having)use "Key Change" column to and define logic to perform aggregation.
An example on cluster key change column: a new column "Key Change" will be added by Datastage and the first row for account number will be 1 and remaining duplicate rows will have 0.
ActNo COLA COLB KeyChange
1 A B 1
1 C D 0
1 E F 0
2 S T 1
2 U v 0
3 W X 1
Aggregator is creepy, I avoid using it; unless its absolutely necessary.
If ActNo is one of the key columns and is at the higher level, then it would be an additional benefit. Assuming it is; I would sort the data on ActNo and hash partition on ActNo and enable "Create Cluster key change column". In the transformer stage (that you may be having)use "Key Change" column to and define logic to perform aggregation.
An example on cluster key change column: a new column "Key Change" will be added by Datastage and the first row for account number will be 1 and remaining duplicate rows will have 0.
ActNo COLA COLB KeyChange
1 A B 1
1 C D 0
1 E F 0
2 S T 1
2 U v 0
3 W X 1
Aggregator is creepy, I avoid using it; unless its absolutely necessary.
Shantanu Choudhary
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes it is. You need an additional output column for the count, set it up with Non Missing Values Count (or Missing Values Count) as its calculation function. If you need both, derive both and sum them in a following 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.
Include a count (*) when you are grouping in your source query
select AccountNumber, count(*)
from XXX
group by AccountNumber
count(*) gives you the number of AccountNumber's in your grouping.
Now you can carry this count(*) coulmn forward to your Aggregator, select the Aggregate function say "First".
You can do your calcluations on other columns as you want.
select AccountNumber, count(*)
from XXX
group by AccountNumber
count(*) gives you the number of AccountNumber's in your grouping.
Now you can carry this count(*) coulmn forward to your Aggregator, select the Aggregate function say "First".
You can do your calcluations on other columns as you want.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.