topic Re: tAggregation groupby is not working in Data Quality

tAggregation groupby is not working

Anonymous — Sat, 16 Nov 2024 05:55:16 GMT

Hello All,

I have fromTime and toTime columns and by using Datediff i did calculated the minutes of each employees each day.

Sample Data:-

136777|16-01-2019|03:00:00 PM|08:00:00 PM|300
136777|10-01-2019|07:00:00 PM|08:00:00 PM|60
136777|02-01-2019|08:00:00 PM|09:00:00 PM|60
136777|11-01-2019|10:00:00 PM|11:00:00 PM|60
136777|22-01-2019|09:00:00 AM|11:50:00 AM|170
136777|28-01-2019|10:00:00 AM|03:00:00 PM|300
136777|31-01-2019|08:00:00 PM|10:00:00 PM|120
136777|04-02-2019|08:00:00 PM|09:00:00 PM|60
136777|05-02-2019|08:00:00 PM|09:00:00 PM|60
136777|06-02-2019|07:00:00 PM|09:00:00 PM|120
136777|11-02-2019|08:00:00 PM|09:00:00 PM|60
136777|15-02-2019|08:00:00 PM|09:00:00 PM|60
136777|20-02-2019|07:30:00 PM|08:00:00 PM|30
136777|21-02-2019|07:00:00 PM|07:30:00 PM|30
136777|22-02-2019|07:30:00 PM|08:15:00 PM|45
136777|28-02-2019|01:30:00 PM|06:30:00 PM|300
136777|01-03-2019|07:30:00 PM|09:30:00 PM|120
136777|06-03-2019|09:05:00 AM|11:03:00 AM|118
136777|07-03-2019|09:00:00 AM|02:00:00 PM|300
136777|07-03-2019|03:00:00 PM|06:00:00 PM|180
136777|06-03-2019|07:00:00 PM|08:00:00 PM|60
136777|11-03-2019|08:00:00 AM|08:30:00 AM|30
136777|11-03-2019|08:00:00 PM|09:30:00 PM|90
136777|13-03-2019|09:05:00 AM|01:54:00 PM|289
136777|13-03-2019|06:30:00 PM|07:30:00 PM|60
136777|21-03-2019|07:00:00 PM|07:30:00 PM|30
136777|23-03-2019|11:00:00 AM|01:00:00 PM|120
136777|25-03-2019|05:30:00 PM|07:40:00 PM|130
136777|27-03-2019|09:15:00 AM|02:03:00 PM|288
136777|27-03-2019|06:30:00 PM|07:30:00 PM|60
136777|29-03-2019|05:30:00 PM|07:39:00 PM|129
136777|01-04-2019|07:00:00 PM|09:00:00 PM|120
136777|02-04-2019|07:00:00 PM|09:00:00 PM|120
136777|06-04-2019|07:00:00 PM|09:00:00 PM|120
136777|09-04-2019|08:00:00 PM|11:30:00 PM|210
136777|10-04-2019|07:00:00 PM|08:00:00 PM|60
136777|11-04-2019|08:00:00 PM|11:00:00 PM|180
136777|15-04-2019|02:23:00 PM|06:15:00 PM|232
136777|17-04-2019|06:30:00 PM|07:30:00 PM|60

tMap:

When i group by EmpID(1st Column) and Date(Second Column) using tAggregate as below image

The output is still same

Can anyone please correct me where i am going wrong.

Regards,

Mohan V.

Re: tAggregation groupby is not working

Anonymous — Mon, 06 May 2019 06:31:10 GMT

Can anyone please correct me whats wrong with my configurations.

Re: tAggregation groupby is not working

act — Mon, 06 May 2019 14:04:24 GMT

Hi! A "Date" object in java encapsulates a "Long" number , starting at 1970-01-01 with millisecond precision.

Formatting it using a pattern, like "YYYY-MM-DD" doesn't change the actual value of the object.

As a consequence, you cant' group Date objects by "same day" but only by "same millisecond"...

To achieve that, first you "format" your date according to what you need for grouping (i.e : day => "YYYY-MM-DD") , then you convert that field of type "Date" into String (usint tConvertType or automatic conversion in tMap). Then you should be able to aggregate on "same day" using that String.

Re: tAggregation groupby is not working

Anonymous — Fri, 17 May 2019 04:49:31 GMT

@act thanks for the reply.

I did tried as below image, by getting day, month, year and then parsing those values as date by using "-".

It still didnt worked.

But still after aggregating it giving me the same output without any change.

I do not understand why this is happening so.

Please help.