topic Re: tAggregateRow - simple count based on group by in Talend Studio

tAggregateRow - simple count based on group by

jimbo20814 — Sat, 16 Nov 2024 02:43:53 GMT

Hi, this is such a simple task but can't seem to get it to work in tAggregateRow. Let's say I have the below input dataset..

From Date ConvID

Jim April 2 54224878

Jim April 2 41357884

Joe April 2 56784333

So all I'm looking to output is the below. Count number of records grouped by From and Date, simple. But no matter what I try in Operations in tAggregateRow, count/sum across different columns it just keeps returning three records (not two) thus does not count the number of records via the group by (From and Date). What am I missing?

From Date Count

Jim April 2 2

Joe April 2 1

Re: tAggregateRow - simple count based on group by

manodwhb — Fri, 10 Apr 2020 01:19:43 GMT

Can you share taggregaterow settings? You need to keep group by on from an date .

Re: tAggregateRow - simple count based on group by

manodwhb — Fri, 10 Apr 2020 02:22:08 GMT

Please share the input and output schema of taggregaterow

Re: tAggregateRow - simple count based on group by

jimbo20814 — Fri, 10 Apr 2020 15:47:33 GMT

I've pinpointed the issue down to aggregating on the date. Please see below screenshots. One is the component and output when only aggregating on from_address, works. When I add timestamp_Date, Talend thinks it's different dates or something because then it's not aggregating but as you can see, it's the same date (2020-04-06). I also tried just aggregating on timestamp_Date, removing from_address, it's it still thinks the dates are different.

Re: tAggregateRow - simple count based on group by

manodwhb — Fri, 10 Apr 2020 16:27:59 GMT

Your dataset is csv then open in notepad ++ or some other editor and check the date column value. I am sure that date values are differing.

Re: tAggregateRow - simple count based on group by

jimbo20814 — Fri, 10 Apr 2020 17:02:10 GMT

Bah, thank you. I've run into this issue before and forgot. If you have data in "yyyy-MM-dd HH:mm:ss" format and you assign to output column of type "yyyy-MM-dd" and even though in tLogRow it doesn't show the hours/minute/seconds, the hours/minutes/seconds are actually still a part of the value. Very misleading. Anyways, I changed the value from datetime to date (via formatDate and parseDate) and now the aggregation is working. Thank you.

Re: tAggregateRow - simple count based on group by

LCornejo — Tue, 23 May 2023 04:45:06 GMT

I had the same problem and I solved it changing the date to string with the tconvert component