topic Latest two rows by Contract in App Development

Latest two rows by Contract

Ayoub — Fri, 15 Nov 2024 21:14:55 GMT

Hello Community ,
I want to display the two latest rows by Contract .
I have a data Set Like this

Contract	Event	Concat	Similarity
765231	1	DAP_DAP_01	100%
765231	2	DAP_DA_01	94%
765231	3	DAP DA_DA_01	76%
632789	1	DO_SG_22	100%
632789	2	DO_ST_22	94%
632789	3	DO_SG_22	94%

And I want the result to be like this

Contract	Event	Concat	Similarity
765231	2	DAP_DA_01	94%
765231	3	DAP DA_DA_01	76%
632789	2	DO_ST_22	94%
632789	3	DO_SG_22	94%

I have used the expression below , but it doesn't give me the result i want :

Sum({<Event={"=rank(Total Event)<=2"}>}1)

Can u Help Me please .

Regards

Re: Latest two rows by Contract

sidhiq91 — Sat, 25 Nov 2023 11:39:48 GMT

NoConcatenate
Temp:
Load * ,AutoNumber(Event, Contract) as Rank,Contract&'-'&AutoNumber(Event, Contract) as Key
Inline [
Contract, Event, Concat, Similarity
765231, 1, DAP_DAP_01, 100%
765231, 2, DAP_DA_01, 94%
765231, 3, DAP DA_DA_01, 76%
632789, 1, DO_SG_22, 100%
632789, 2, DO_ST_22, 94%
632789, 3, DO_SG_22, 94%
];

NoConcatenate
Temp1:
Load Contract&'-'& Max(Rank,1) as Key
Resident Temp
group by Contract;
Concatenate
Load Contract&'-'& Max(Rank,2) as Key
Resident Temp
group by Contract;

Inner join (Temp1)
Load * Resident Temp;

Drop table Temp;
Drop Fields Key, Rank from Temp1;

Exit Script;