Is there count without using aggregator

[rrsivaraghava]

Hi,
IS there any way in datastage to count group by columns..suppose for example one project manager have 4 projects and the other have two projects.then we like to have the count of projects of each project manager projects.let name of project manager is one column and list of projects is another columns.without using aggregator

[sachin1]

using stage variable will help.

[ray.wurlod]

Welcome aboard.

The answer is yes, but why would you want to? That's what the Aggregator stage is for.

And it works MUCH, MUCH BETTER if the data are sorted on the grouping key(s) and you assert this fact on the input link to the Aggregator.

The Transformer stage method REQUIRES sorted input to be sensible about identifying change of group.

[rrsivaraghava]

Hi,
IS there any way in datastage to count group by columns..suppose for example one project manager have 4 projects and the other have two projects.then we like to have the count of projects of each project manager projects.let name of project manager is one column and list of projects is another columns.without using aggregator

how to do by using stage variable.how to count the group by columns

[sachin1]

check with the post below

Can we do sum in transformer stage?If yes how?

see the answer from baglasumit21

[bkumar103]

Hi,
IS there any way in datastage to count group by columns..suppose for example one project manager have 4 projects and the other have two projects.then we like to have the count of projects of each project manager projects.let name of project manager is one column and list of projects is another columns.without using aggregator

define a stage variable stgcount as initial value 1, stgvar1 with initial value '' use the following derivation in stage variable

stgcount -> if projectmanager = stgvar1 then stgcount +1 else 1
stgvar1 -> projectmanager

In the column derivation just use the stgvar1

after transformaer use the remove duplicate stage, projectmanager as the key, keeping the last record

hence the job design would be

input----->Transformer--->remove duplicate---->output

[rrsivaraghava]

not the row number.i want the count of project for that project manager.suppose projectmanager1 has 4 projects then the count should be 4.if we has two projects then count should be 2

[bkumar103]

just try to implement the approach i have suggested. suppose your record look like as follow

Project manager Name of projest
------------------------------------------------
1 X
2 X
1 Y
1 Z
2 Y

then the job will give

Project manager No of project
---------------------------------------------
1 1
1 2
1 3
2 1
2 2

Now the remove duplicate stage will remove the records keeping the last.

Project manager No of project
---------------------------------------------
1 3
2 2


Hope i understand you problem and this make sense...

Note... Records should be sorted on " project manager" before using the approach.


Thanks,
Birendra

[rrsivaraghava]

can u plz tell the detail stage variable for this

[rrsivaraghava]

can u plz tell the detail stage variable for this

if it has to be done then it should be sorted or not necessary

[ray.wurlod]

can u plz tell the detail stage variable for this

U hasn't logged in to DSXchange for a couple of months. I assume that plz is a town in the Balkans somewhere.

[rrsivaraghava]

can we do this without using sort stage.

[ray.wurlod]

Yes of course. By using an Aggregator stage. Or by using pre-sorted data.

[bkumar103]

You can get it done thru small unix script where you dont need any stages.

[rrsivaraghava]

is it possible without unix,aggregater and sort