DSXchange

Hi all,

My requirement is to pull top 3 values based on Key column A.
my input data is like:

ColumnA ColumnB
A 90.1
B 80.4
A 70.3
C 30.0
A 10.1
A 50.4
B 20.5
B 03.5

Required output:top 3 values of column B and columnA should also be in asc order

A 90.1
A 70.3
A 50.4
B 80.4
B 20.5
B 03.5

Please guide me how to get this

Thanks in advance.

WHat have you tried? It should be a straightforward sorting and filtering specification.

Hi Ray,

I have done by using sort stage and in transformer i have limited the output to 3 rows . but i need to get 3 records for each key column.

I have used sort stage and limited the output to 3 rows.

But i need to get top 3 columnB values based on each key values

Source--->SortStage(Sort Column A Ascending and ColumnB Descending and Partition on Column A)-->HeadStage (No. of rows per partition=3, partitioning Same)-->Target

Given in sort stage Column A as ASC and ColumnB DESC,then you have mentioned about Partitioning and head stage.Could you please elobarate on this.

Theres not much to elaborate, but let me give it a try.

In the Sort Stage Hash Partition the data on Column A. In the HeadStage keep partitioning as Same. The head stage asks you for the no. of rows you require per partition, mention 3 there.

Thanks a lot .
Its working Fine.

hi,

iam unable get 3 records for each key column.
Its showing only for some key values.
Please guide me.

nani0907 wrote:hi,

iam unable get 3 records for each key column.
Its showing only for some key values.
Please guide me.

Ensure that you have partitioned the data "only" on Column A in the Sort Stage and have kept the partitoning "Same" in the Head Stage.

nani0907 wrote:hi,

iam unable get 3 records for each key column.
Its showing only for some key values.
Please guide me.

use transformer then and using stage variables restrict the count to 3.

assign a count to each row after sorting and hash partitioning the input. and inly pass top 3 values using constraints.

Ater Sort stage ,i have transformer and used the partioning method same and then head stage with same partion. still the same issue.

nani0907 wrote:Ater Sort stage ,i have transformer and used the partioning method same and then head stage with same partion. still the same issue.

I am not talking about head stage. It returns 3 records per partition,

If my concepts are correct there may be more than one key value present in one partition. But hash partiotioning ensures that records with same key value goes to one partition. Remove head stage and use transformer. as explained earlier.

Though i have used transformer, getting the same results.Not populating for all the key values.

please help me out.

Sequential file --> copy--> copy--->transformer-->aggregator
| |

Join <--- filter
|
output