tAggregate return zero with null values on average aggregation

Highlighted
Five Stars

tAggregate return zero with null values on average aggregation

Hi,

 

Taggregaterow return zero value on null values with the average aggregation.

I tried this example https://help.talend.com/reader/atTvdntoupqchAynyb03~Q/YYVSsDiyJ3vBHh9357Ds~Q

and as you can see on the picture, the average for Thomas is "0"...

 

I really need to have null value for this type of operation with talend, have you an idea to fix this problem???

Thanks in advance for your help,

 

Best regards,

Rosalien


Accepted Solutions
Employee

Re: tAggregate return zero with null values on average aggregation

@Rosalien 

 

From my personal view, it is not a bug as it is still giving results after avoiding the null value.

 

But I would welcome you to raise it as a bug using below link so that you can get the view of Talend Product Team on this scenario.

 

https://jira.talendforge.org

 

Please add the sample data, current output value and your expected output value so that they will get more idea about your specific use case.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

View solution in original post


All Replies
Eleven Stars

Re: tAggregate return zero with null values on average aggregation

You are right,

default value of Average is 0 irrespective of "Ignore NULL values" but it is not the case of Sum operation.

 

@rhall_2_0 @nikhilthampi  Not sure if it a bug or expected result.

 

 

 

 

 

Regards
Abhishek KUMAR
Employee

Re: tAggregate return zero with null values on average aggregation

Hi,

 

   Please select ignore null value option for your use case.

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: tAggregate return zero with null values on average aggregation

Thanks you for your quick reply.

But unfortunately, I ever checked "null value option". I really don't understand why for average operation it return zero value and for other operation (like SUM, Min, Max) it return correctly "null"

Is-it possible to do aggregate operation with tJavarow??

 

Thanks for your help

 

Employee

Re: tAggregate return zero with null values on average aggregation

Hi,

 

   Could you please provide some 5 or 6 sample records to play around? Let us see how it is responding.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: tAggregate return zero with null values on average aggregation

My job is too complex but I reproduce the same "bug" with this simple example

TFixedFLowInput > tAggregateRow> tSortRow > tLogRow

 

In TFixedFLowInput (two columns name String and score Double (same bug with float and bigdecimal)

James;93
Thomas;
Peter;94
James;96
Thomas;
Peter;96
James;92
Thomas;
Peter;95
James;96
Thomas;
Peter;98
James;97
Thomas;

 

I hope it's  sufficient to see how it is responding...

I use Talend Open Studio for Data Integration Version: 7.1.1 on Ubuntu 18-04

 

Thanks you for your help!

 

Employee

Re: tAggregate return zero with null values on average aggregation

Hi,

 

   I am getting the answer as below.

image.png

 

image.pngtaggregaterow

 

It seems to work fine when you are having right data type. Now, if you want to calculate zero for null, I would suggest to replace null with zero in the previous step and then do the calculation. You can replace zero back to null after aggregation.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Eleven Stars

Re: tAggregate return zero with null values on average aggregation

@nikhilthampi

If you do same work with “sum” not “avg”, you will get null not zero ?

@Rosalien

after taggregaterow , in javarow , check if column ‘sum’ is null then make column ‘avg’ also null.
Regards
Abhishek KUMAR
Employee

Re: tAggregate return zero with null values on average aggregation

Hi,

 

For sum, it will be null, if there are no qualifying input records. If the there is some not null records (like in example of James), it will calculate all the remaining not null records.

 

For average, it will be always 0 if all the inbound records are null. This happens because avg = Sum of inbound records/Number of inbound qualifying records.

 

In the case of zero qualifying inbound records, it cannot do a division by zero as it will generate error. So it is adding the default value as 0 in this case.

 

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Eleven Stars

Re: tAggregate return zero with null values on average aggregation

Thanks Nikhil.
Regards
Abhishek KUMAR
Five Stars

Re: tAggregate return zero with null values on average aggregation

Ok, thanks for these explanations...

I'm agree with you but when you check 'ignore null value', you expected it return null value because it ignore null value.

For me, it's a bug because it didn't give the real value expected, a null value.

Do you agree with that ? Is-it possible to workaround ?

In my case, I can't replace zero by null because zero value can be a real value...

Thanks for your time,

 

 

Employee

Re: tAggregate return zero with null values on average aggregation

@Rosalien 

 

From my personal view, it is not a bug as it is still giving results after avoiding the null value.

 

But I would welcome you to raise it as a bug using below link so that you can get the view of Talend Product Team on this scenario.

 

https://jira.talendforge.org

 

Please add the sample data, current output value and your expected output value so that they will get more idea about your specific use case.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

View solution in original post

Five Stars

Re: tAggregate return zero with null values on average aggregation

Ok, I will report it in the forge.
Thank you for taking the time to help me.
Have a nice day,
Best regards,
Five Stars

Re: tAggregate return zero with null values on average aggregation

I post the bug but I show a workaround (not the best...). I use tAggregateRow to calculate count and sum and after use tJavaRow with condition to calculate average (if sum==null, then average is null) :

 

if(input_row.sum==null){
output_row.average = null;
}else{
output_row.average = input_row.sum/input_row.count;
}

 

 

Capture d’écran de 2019-04-24 10-56-21.png

 

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog