Aggregator

rajasingam · Post by **rajasingam** » Fri Mar 28, 2008 4:08 am

Hey Friends,

I have an Input File as follows;

KeyID EmpId1 EmpId2 Col3_1 Col3_2
101 1043 0 A 0
101 0 1059 0 B
101 0 0 0 0
101 0 0 0 0
102 0 0 0 0
102 0 0 0 0
103 1043 0 Y 0
103 0 0 0 0
104 0 1059 0 Z
104 0 0 0 0
104 0 0 0 0
104 0 0 0 0

Using Aggregator Stage I mentioned;

1. In the group - KeyID
2. In the column for calculation - EmpId1
3. Sum of calculation - EmpId1
4. Repeated the Steps 2 and 3 for other columns too

Expected Output;

KeyID EmpId1 EmpId2 Col3_1 Col3_2
101 1043 1059 A B
102 0 0 0 0
103 1043 0 Y 0
104 0 1059 0 Z

But I had an Output as follows;

KeyID EmpId1 EmpId2 Col3_1 Col3_2
101 1043 1059 0 0
102 0 0 0 0
103 1043 0 0 0
104 0 1059 0 0

For Integer(EmpId1 and EmpId2) we can sum the Input But for an Alphabet(Col3_1 and Col3_2) What to do?

Please help me out

Regards,
Rajasingam.

BugFree · Post by **BugFree** » Fri Mar 28, 2008 4:23 am

You tell us what to do? How do you expect the string to be calculated? Do you want a concatenated string for Col3_1 and Col3_2?

ray.wurlod · Post by **ray.wurlod** » Fri Mar 28, 2008 5:09 am

You can't include "A" and "B" as values to be summed. DataStage substitutes zero, and probably logs a warning of the form "non-numeric where numeric required, zero used".

rajasingam · Post by **rajasingam** » Fri Mar 28, 2008 5:38 am

Do you want a concatenated string for Col3_1 and Col3_2?

No I don't want to Concatenate the Columns, Need to sum it. Is there any options to do that in Aggregator?

rajasingam · Post by **rajasingam** » Fri Mar 28, 2008 5:42 am

If Col3_1 and Col3_2 cant be summed Because they are Strings then How to achieve the Output using Aggregator?

BugFree · Post by **BugFree** » Fri Mar 28, 2008 6:04 am

Tell me the answer for the b+y= and b+z= first? Then i will tell you how it can be done in DataStage.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 28, 2008 4:47 pm

To put it another way, only numeric values can be summed, averaged or have any other numeric function applied.

rajasingam · Post by **rajasingam** » Sat Mar 29, 2008 2:18 am

Then what stage I have to Use to get the answer?

ArndW · Post by **ArndW** » Sat Mar 29, 2008 2:46 am

There is not stage that can perform mathematic operations on non-mathematic objects. You need to define what the result of "B+Z" is, as BugFree has noted. Once you have a rule for that you can either convert your values to numerics and use an Aggregator stage or you can write your own code to do this.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 29, 2008 4:45 pm

You can do Min, Max, or Count on non-numeric data. You can NOT do Sum, Avg or Std Dev. Most of the other functions available also require that the data be numeric.