DSXchange

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.

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?

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

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?

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

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

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

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

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.

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.