question

holomew avatar image
0 Votes"
holomew asked KranthiPakala-MSFT answered

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

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

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @bartnoble

Just checking in to see if the below suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.

0 Votes 0 ·

Hi @bartnoble

We still have not heard back from you. Just wanted to check if the below suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

1 Vote 1 ·
MarkKromer-MSFT avatar image
1 Vote"
MarkKromer-MSFT answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
2 Votes"
KranthiPakala-MSFT answered

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.

106790-image.png

Here is the GIF:

106885-dataflowaggregateadddatecolumn.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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.