How to Find All Columns Except for the Grouping Column of the Row(s) with Max/Min Value in Each Excel Group
Problem description & analysis
Below is part of an Excel file which has three columns - first name, last name and birthday:
A |
B |
C |
|
1 |
Lukas |
M. |
14.1.2000 |
2 |
Lukas |
B. |
14.2.2000 |
3 |
Lukas |
A. |
15.1.2000 |
4 |
Lukas |
C. |
30.1.2000 |
5 |
Max |
A. |
15.1.2000 |
6 |
Max |
B. |
15.2.2000 |
7 |
Max |
C. |
16.1.2000 |
8 |
Max |
D. |
31.1.2000 |
The task is to group the data by first name, find the row with the smallest age in each group and export the first and third columns. The expected result is as follows:
A |
B |
|
1 |
Lukas |
14.2.2000 |
2 |
Max |
15.2.2000 |
Here the record with the maximum value is obtained.
Solution & explanation
Write and execute the following esProc script:
A |
|
1 |
=file("data.xlsx").xlsimport() |
2 |
=A1.group(_1;~.top@1(-1,_3)) |
3 |
=file("result.xlsx").xlsexport() |
The maxp function in the script calculates the record with the maximum value (the bigger the date value, the smaller the age).
https://stackoverflow.com/questions/64117170/how-to-filter-for-oldest-value-in-excel
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
