# Top 2 transactions per date

I have a scenario like below:
Date Amt
01-01-2016 100
01-01-2016 300
02-01-2016 350
01-01-2016 200
02-01-2016 150
02-01-2016 400
01-01-2016 250
02-01-2016 250
I want to find top to transactions on each date:
Date Amt
01-01-2016 300
01-01-2016 250
02-01-2016 400
02-01-2016 350

Showing Answers 1 - 11 of 11 Answers

#### Komal

• Sep 19th, 2016

Input File ---> Sort(cyc_dt,AMt Dsc) -----> Scan(cyc_dt)---> Reformat --->output

used Below Code
Dml of input file:
record
date("DD-MM-YYYY")("") cyc_dt;
decimal("
") amt;
end

DML Of Scan
record
decimal(" ") cnt;
date("DD-MM-YYYY")("") cyc_dt;
decimal("
") amt;
end

Code Inside the Scan:

/*Temporary variable*/
type temporary_type=record
decimal(5) cnt_chk;
end;

temp :: initialize(in) =
begin
temp.cnt_chk:: 0;
end;

temp :: scan(temp, in) =
begin
temp.cnt_chk :: temp.cnt_chk + 1;
end;

out :: finalize(temp, in) =
begin
out.cnt:: temp.cnt_chk;
out.* :: in.*;
end;

out :: output_select(out)
begin
out :: out.cnt <=2;
end;

Dml of Reformat:
record
date("DD-MM-YYYY")("") cyc_dt;
decimal("
") amt;
end

#### sonalika

• Sep 21st, 2017

Use rollup
sort - {date}
rollup - {date} - vector_sort(vector_append(temp_trnx, in.trnx))
finalize - out.top_trnx :: temp_trnx[0]
out.top2_trnx :: temp_trnx[1] (you will be getting top 2 values in one record)
or
you can use scan as vector operations impact performance
sort - {date}, {trnx desc}
scan - {date} - use a new column rank and increase its value by 1 for each record in a group.
output_select - select count<=2
(you will be getting two records per date group in output)

#### Speedy

• Sep 22nd, 2017

You can put the records in a scanner with a temp variable count and get the count of the records. In the end you can pull all the records where count<=2.

#### Atul Raut

• Nov 8th, 2017

Use Rollup component. Select Date field as key and Max function on amount field in Rollup component.

#### Ketan Khot

• Nov 29th, 2017

You can use below components in your graph
input file --> sort ({date,amount desc}) --> dedup_first ({date},keep first) -->(select port output )
dedup deselect port-->dedup_second ({date},keep first) -->merge({date})(first dedup and second dedup select ) --> output file

#### Uma Rathod

• Dec 5th, 2017

I like to do one correction. In Dedup sort keep key only date then only you will get the expected result.

#### JAtin

• Apr 30th, 2018

1. Sort by Date , Transaction amount desc
2. Scan and use Date as key and calculate count() field as name Seq.
3. Filter by expression select Seq <=2

#### Sagar Sinha

• Jun 24th, 2018

We can use in memory sort over the date column which will be the key and then using the max function over the amount. We will get desire result

#### snehal

• Aug 8th, 2018

Use sort component with date as key and transaction amount in descending order. After that use rollup with date as key and then take max (transaction amount). So for each date you get max amount transaction.

#### Krushna

• Sep 7th, 2018

Sort on date and amount(descending) then use scan to assign number within group then filter records whose number <=2

#### Amaan Sajid Ansari

• Nov 14th, 2018

Use SORT---> SCAN (use scan select function and set the value as < =2)