Select value from column based on value of another column for each distinct entity

[QS_]

I have some data similar to the following table.

UNQ	route_name	last_sent
2023-12-01 00:05 QE SFN FACT SMJP	E/MFT/R	0
2023-12-01 00:05 QE SFN FACT SMJP	E/MFT/R	1
2023-12-01 00:05 QR 720 KSEA OTHH	DEFRTE	0
2023-12-01 00:05 QR 720 KSEA OTHH	MCT/R	0
2023-12-01 00:05 QR 720 KSEA OTHH	MFT/R	0
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X30XX	0
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X31	0
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X31	1
2023-12-01 00:10 QR 8622 OTHH VOMM	20E-MAA	0
2023-12-01 00:10 QR 8622 OTHH VOMM	40-MAA	0
2023-12-01 00:10 QR 8622 OTHH VOMM	40-MAA	1
2023-12-01 00:10 QR 8622 OTHH VOMM	42E-MAA	0

I need to create a new column named last_sent_route that selects the route name where last_sent is 1 for each distinct UNQ.

UNQ	route_name	last_sent	last_sent_route
2023-12-01 00:05 QE SFN FACT SMJP	E/MFT/R	0	E/MFT/R
2023-12-01 00:05 QE SFN FACT SMJP	E/MFT/R	1	E/MFT/R
2023-12-01 00:05 QR 720 KSEA OTHH	DEFRTE	0	SEA-X31
2023-12-01 00:05 QR 720 KSEA OTHH	MCT/R	0	SEA-X31
2023-12-01 00:05 QR 720 KSEA OTHH	MFT/R	0	SEA-X31
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X30XX	0	SEA-X31
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X31	0	SEA-X31
2023-12-01 00:05 QR 720 KSEA OTHH	SEA-X31	1	SEA-X31
2023-12-01 00:10 QR 8622 OTHH VOMM	20E-MAA	0	40-MAA
2023-12-01 00:10 QR 8622 OTHH VOMM	40-MAA	0	40-MAA
2023-12-01 00:10 QR 8622 OTHH VOMM	40-MAA	1	40-MAA
2023-12-01 00:10 QR 8622 OTHH VOMM	42E-MAA	0	40-MAA

I am unable to figure out a solution. Can someone guide me on how to implement it?

[Ahidhar · Accepted solution]

try this in script

tab:
load * inline
[
UNQ ,route_name ,last_sent
2023-12-01 00:05 QE SFN FACT SMJP ,E/MFT/R, 0
2023-12-01 00:05 QE SFN FACT SMJP ,E/MFT/R ,1
2023-12-01 00:05 QR 720 KSEA OTHH ,DEFRTE ,0
2023-12-01 00:05 QR 720 KSEA OTHH ,MCT/R ,0
2023-12-01 00:05 QR 720 KSEA OTHH ,MFT/R ,0
2023-12-01 00:05 QR 720 KSEA OTHH ,SEA-X30XX, 0
2023-12-01 00:05 QR 720 KSEA OTHH ,SEA-X31 ,0
2023-12-01 00:05 QR 720 KSEA OTHH ,SEA-X31 ,1
2023-12-01 00:10 QR 8622 OTHH VOMM ,20E-MAA ,0
2023-12-01 00:10 QR 8622 OTHH VOMM ,40-MAA ,0
2023-12-01 00:10 QR 8622 OTHH VOMM ,40-MAA ,1
2023-12-01 00:10 QR 8622 OTHH VOMM ,42E-MAA ,0
];

tab1:
mapping load
UNQ,route_name as last_sent_route
resident tab where last_sent=1;

NoConcatenate
tab2:
load *,applymap('tab1',UNQ) as last_sent_route
resident tab; drop table tab;

or try this in front end

=aggr(only({<last_sent={'1'}>}route_name),UNQ)

[QS_]

Thank you for your response! I used the front-end solution you provided, and it works perfectly!