Top values in desc order based on key column

nani0907 · Post by **nani0907** » Tue Dec 02, 2008 11:31 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 03, 2008 12:23 am

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

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 12:29 am

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.

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 1:52 am

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

bi_fujitsu · Post by **bi_fujitsu** » Wed Dec 03, 2008 2:55 am

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

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 3:22 am

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.

bi_fujitsu · Post by **bi_fujitsu** » Wed Dec 03, 2008 3:28 am

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.

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 3:37 am

Thanks a lot .
Its working Fine.

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 3:50 am

hi,

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

bi_fujitsu · Post by **bi_fujitsu** » Wed Dec 03, 2008 3:55 am

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.

priyadarshikunal · Post by **priyadarshikunal** » Wed Dec 03, 2008 3:58 am

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.

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 4:04 am

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

priyadarshikunal · Post by **priyadarshikunal** » Wed Dec 03, 2008 4:11 am

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.

nani0907 · Post by **nani0907** » Wed Dec 03, 2008 4:33 am

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

please help me out.

srinivas.g · Post by **srinivas.g** » Wed Dec 03, 2008 6:13 am

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

Join <--- filter
|
output