Expand my Community achievements bar.

SOLVED

how to use max function in query activity

Avatar

Level 4

hi all, i have data like this,

 

IdStatusDelivery Iddate
1231010015-08-2023
1231020016-08-2023
1231030017-08-2023
1231040018-08-2023
1231050020-08-2023
3461020018-08-2023
3461030019-08-2023

and i need output like this

IdStatusDelivery Iddate
1231050020-08-2023
3461030019-08-2023

so basically my requirement /conditions are

 for each id, i need max of delivery id and last modified date should be on and after 15-08-2023 and should be max of last modified date

 

i need to club all these conditions in adobe campaign query activity

please help me

how can i set query actiivty for this

 

please help me here, tried multiple hit and try but no luck\

 

 

and

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Shrutii ,

You can achieve your use case using Query and deduplication activity.

 

ParthaSarathy_0-1692595470933.png

 

First in Query, create a filtering condition as DATE on or after 15-08-2023 00:00:00 (select date from drop-down calendar)

 

Next in Deduplication activity > edit configuration > targeting dimension: temporary schema >next> select '@id' in identification of duplicate >next>

ParthaSarathy_1-1692595568809.png

ParthaSarathy_2-1692595594900.png

Duplication method:

Doubles to keep: 1

Method: using an expression

Expression: select 'Delivery ID'

And select 'keep the records with the largest value'

ParthaSarathy_3-1692595633197.png

 

Run the workflow.

Now the output of Deduplication activity will satisfy your requirement.

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

Hi @Shrutii ,

You can achieve your use case using Query and deduplication activity.

 

ParthaSarathy_0-1692595470933.png

 

First in Query, create a filtering condition as DATE on or after 15-08-2023 00:00:00 (select date from drop-down calendar)

 

Next in Deduplication activity > edit configuration > targeting dimension: temporary schema >next> select '@id' in identification of duplicate >next>

ParthaSarathy_1-1692595568809.png

ParthaSarathy_2-1692595594900.png

Duplication method:

Doubles to keep: 1

Method: using an expression

Expression: select 'Delivery ID'

And select 'keep the records with the largest value'

ParthaSarathy_3-1692595633197.png

 

Run the workflow.

Now the output of Deduplication activity will satisfy your requirement.