tAggregateRow - simple count based on group by

[jimbo20814]

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

[manodwhb]

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

[manodwhb]

Please share the input and output schema of taggregaterow

[jimbo20814]

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.

[manodwhb · Accepted solution]

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.

[jimbo20814]

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.

[LCornejo]

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