This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Select N largest from each group

pranjbhatt
New Contributor II

2 weeks ago

Hi, 

I am building a pipeline to select latest datetime from the data file. I also need the latest value for all source_code (essentially a groupby and then max value of within group). I tried Aggregate function to select MAX over datetime and in the GROUPBY field I added source_code but do not receive the intendent result.

Is there other way ? I have sorted the columns I need to find MAX value.  

Reply
2 REPLIES 2

koryknick
Employee
Employee

2 weeks ago

@pranjbhatt - can you provide a small sample of input data and desired output to clarify your question?

0 Kudos
Reply

pranjbhatt
New Contributor II
In response to koryknick

2 weeks ago

Hi, 

Following is the sample data. I need to have latest last_date values for each of the source (source_code) and each of the target table.  Note that date is in dd-MM-yyyy format.

 

INPUT     
TARGET TABLESOURCE CODELAST DATE RUN DATELOAD STATUS TYPE
DIM_CLIENTconcentrix03-09-202403-09-2024NowPositive
DIM_CLIENTconcentrix30-08-202403-09-2024NowPositive
DIM_CLIENTenvizi01-09-202403-09-2024NowPositive
DIM_CLIENTenvizi27-08-202403-09-2024NowPositive
USERenvizi28-08-202403-09-2024NowPositive
USERconcentrix02-09-202403-09-2024NowPositive
USERconcentrix29-08-202403-09-2024NowPositive
USER envizi27-08-202403-09-2024NowPositive

 

OUTPUT     
TARGET TABLESOURCE CODELAST DATE RUN DATELOAD STATUS TYPE
DIM_CLIENTconcentrix03-09-202403-09-2024NowPositive
DIM_CLIENTenvizi01-09-202403-09-2024NowPositive
USERenvizi28-08-202403-09-2024NowPositive
USERconcentrix02-09-202403-09-2024NowPositive
0 Kudos
Reply