I have few records like this
11, A,B,C
11,A,B,C
10,A,B,C
10,A,B,C
9,A,B,C
suppose the first column is ID. i need all the records which has the maximum id for the combination of 2nd,3rd and 4th column.
My output should be
11,A,B,C
11,A,B,C
how do it do it? Please help. Its urgent
Need help to filter records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If it's urgent, sign up with your official support provider for priority service and learn just how much "urgent" costs.
DSXchange is an all-volunteer site, whose members post as and when they can. We don't do "urgent".
DSXchange is an all-volunteer site, whose members post as and when they can. We don't do "urgent".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Need help to filter records
reshmakr wrote:I have few records like this
11, A,B,C
11,A,B,C
10,A,B,C
10,A,B,C
9,A,B,C
suppose the first column is ID. i need all the records which has the maximum id for the combination of 2nd,3rd and 4th column.
My output should be
11,A,B,C
11,A,B,C
how do it do it? Please help. Its urgent
If 2nd,3rd,4th column always have the same value the a look up on the first column can solve your problem...:D
if they are changing
![Confused :?](./images/smilies/icon_confused.gif)
grimm
Re: Need help to filter records
reshmakr wrote:I have few records like this
11, A,B,C
11,A,B,C
10,A,B,C
10,A,B,C
9,A,B,C
suppose the first column is ID. i need all the records which has the maximum id for the combination of 2nd,3rd and 4th column.
My output should be
11,A,B,C
11,A,B,C
how do it do it? Please help. Its urgent
Use copy stage and make two streams, one into Aggregator and second one into Lookup. Get the maximum required ID from aggregator by grouping remaining 2,3, and 4 columns, after that do lookup and get the values of that pirticular ID (two records as you expected).
hope this will help you....
SasiKiran
Thanks Grimm , Grace and Sasikaran for your replies. I tried using remove duplicate and Join stage and i am getting the desired output.....
I treid aggregator as well.
And thanks ray for your suggetion. I could resolve it with the help of dsxchange and without contacting official support provider.
I guess "urgent" has cost you before.
I treid aggregator as well.
And thanks ray for your suggetion. I could resolve it with the help of dsxchange and without contacting official support provider.
I guess "urgent" has cost you before.
-
- Participant
- Posts: 12
- Joined: Mon Sep 08, 2008 12:40 am
- Location: Hyderabad
Hi
U need the max ID records rt? In this senario how can remove duplicate will work.When u will use Remove duplicate automatically it will remove the duplicate records likereshmakr wrote:T I tried using remove duplicate and Join stage and i am getting the desired output.....
I treid aggregator as well.
.
11,A,B,C
10,A,B,C
How can u will get the MAX ID records in out put?
11,A,B,C
11,A,B,C
Shirishareddy
Yes you can do it using remove duplicate followed by join stage.
I sorted the records such that i get the maximum id on top ad retain first record.
use a copy stage before the remove duplicate. Take anther link from there which has all the records.
now use inner join with maxid as the key.it will givve all the matching records
I sorted the records such that i get the maximum id on top ad retain first record.
use a copy stage before the remove duplicate. Take anther link from there which has all the records.
now use inner join with maxid as the key.it will givve all the matching records