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 - 33 of 33 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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

Atul Raut

  • Nov 8th, 2017
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

Krushna

  • Sep 7th, 2018
 

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

  Was this answer useful?  Yes

Amaan Sajid Ansari

  • Nov 14th, 2018
 

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions