Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Ayoub
Contributor III
Contributor III

Latest two rows by Contract

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

 

 

Labels (2)
1 Reply
sidhiq91
Specialist II
Specialist II

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;