Azure Data Factory: Aggregate By Day But Keep Original Timestamp Column

Question

I would like to aggregate by maximum value for a day.

To achieve this, I'm casting the original timestamp type to a date type and using maximum for that day.

However, I lose the original timestamp which I'd like to keep.

For example, the incoming data:

ID Value Timestamp
ABC 11 2021-06-10T07:33:21
ABC 2 2021-06-10T03:28:04
ABC 14 2021-06-10T05:45:17

So I cast the timestamp to date type then I'm aggregating by maximum(Value) and grouping by ID and the newly-created date column (no timestamp) to get the max value for that ID and day. But I'd like to keep the timestamp also. e.g.:

ID Value Date Timestamp
ABC 14 2021-06-10 2021-06-10T05:45:17

Answer 1

In your Derived Column, create a new column instead of overwriting the original Timestamp column. Call it something like 'date_for_agg'. You can use a Select to remove the temp column from your flow or just drop it in the Sink mapping.

Answer 2

Hi @bartnoble,

In addition to Mark Kromer's input, sharing the implementation GIF below to achieve the above requirement, as it can be beneficial for others reading this thread.

Here is the GIF:

Hope this helps.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.