Aggregation on a single field

vinaymanchinila · Post by **vinaymanchinila** » Tue Apr 19, 2005 7:19 am

Hi I have few columns comming from source, and I need to aggregate only one column and pass the rest of them as it is without applying any logic or aggregation on the rest of the columns.
The isue is when I use the aggregator stage, I HAVE TO select some kind of aggregation on every column , is there a way we can skip it!
Thanks,

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 19, 2005 7:53 am

Select from first, last etc

vinaymanchinila · Post by **vinaymanchinila** » Tue Apr 19, 2005 7:56 am

The other columns have different values which we need, so select from first or last would eliminate the other records !

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 19, 2005 7:57 am

In that case, how can you group one field alone?

Can you explain your logic and what you intend to achieve?

vinaymanchinila · Post by **vinaymanchinila** » Tue Apr 19, 2005 8:01 am

Lets say I have ProdNumbers, Invoice, Sales, Load_Date, all i need is Sum of Invoices, and other fields I need them as it is.
Thanks,

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 19, 2005 8:06 am

You must either group by other fields if they are above the Invoice No. in the food chain or supply one of the distinct values for them if they are below.

Can you supply some sample values and how you wish the output to be.

chulett · Post by **chulett** » Tue Apr 19, 2005 8:08 am

So, you'd want all of these 'non-grouped' fields to have the one grouped field repeated on each record?

I'm a little lost here.

If this is really what you meant, I'd think you'd need to make two passes through the data - once to get the sum per 'key group' and then another pass to associate that sum with every detail record.

vinaymanchinila · Post by **vinaymanchinila** » Tue Apr 19, 2005 8:10 am

Yes i got it by using 2 passes!.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 19, 2005 8:17 am

Even if you have them in 2 passes, how do you relate them again. Can you let us know the hierarchy. i.e. the relationship between the fields. That is why some sample data set will be helpful.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 19, 2005 5:52 pm

Think about how you would do it in a query. You'd almost certainly need a temporary table (virtual or physical) in which to store the keys and associated sums, so that you could join this with the original result set.

Well, no surprise, the processing isn't really any different in DataStage; you just get to draw the pictures rather than creating the SQL.