sum and count function in Aggregate Stage Problem

[somu_june]

Hi,

Please help me . I want to aggregate records on this condition

Group by : Currency and my problem is actually how to find sum(price) and count (Material). When Iam selecting Aggregation Type = Count Rows. Iam not able to select Aggregatio type = calculation for Sum(price).

How to achieve sum and count function in aggregate stage.



Thanks,
Somaraju

[kumar_s]

Hi,

You can still acheive Row count by the Aggregation type as Calculation.
Just assign some Dummy column with a value of 1 in some preceding stage. Sum the Dummy column, so that you will reach the total count of the aggregated value.

-Kumar

[ray.wurlod]

You need to create an extra output column for the sum. You can then associate the sum rows property with this output column.

[somu_june]

Hi ,

I have created a output columm with Dummy and sql as integer and length =1 and I gave value as 1. This dummy is given as input to aggregator to count number of records and Its metadata in ouptut is decimal 8,2.

But Iam getting an error saying

main_program: Syntax error: Error in "group" operator: Error in output redirection: Error in output parameters: Error in modify adapter: Error in binding: Expected '=', got: "$", line 165


Thanks,
somaraju

[ray.wurlod]

Apparently there's a "$" sign in your input data. You can't apply arithmetic functions (like SUM) to non-numeric data.

[somu_june]

Hi Ray,


Iam inputing Sum(price) char(15) it contains numerical value where as my output columm name contain TotalAmount$. Do you think because of this Iam facing a problem.I checked by removing $ but Iam getting same problem. I checked input file there are no values with $



Thanks,
Somaraju

[somu_june]

Hi,

Now I connected Aggregator to the source transformer. Now Iam getting a warning saying



Aggregator_sum: When checking operator: When binding output interface field "TotalAmount" to field "TotalAmount": Converting a nullable source to a non-nullable result;
a fatal runtime error could occur; use the modify operator to
specify a value to which the null should be converted.


Thanks,
somaraju.

[ray.wurlod]

The most recent warning is a complaint about your job design, and a warning that - should a null arrive from the input - your job will abort because the output column is defined as not null. You must fix the design - probably (as suggested) with an upstream Modify stage to intercept the null and replace it with some default value.

[somu_june]

Hi ray,


Thanks for the solution. My problem was solved . I made nullable to yes in both input and output of aggregate stage. Thanks for solving my problem.









Thanks,
Somaraju