Filtering Input Records in Sequential file

[pnchowdary]

Hi Guys,

My Input file is of the below format

Code: Select all

ColA ColB
 10  100
 10  101
 10  102
 10  103
 22  910
 22  911
 22  912

Each Value in ColA has mutliple different values for ColB

My Lookup file is of the below format

Code: Select all

ColA  ColC
 10     2
 22     1

The requirement for my job is that I need to match the input file records based on the value of ColA. Then I need to take the corresponding value of ColC and go to the input file and grab that many number of rows of ColB(from the last, assuming that my input file is sorted based on ColA,ColB).

Code: Select all

For Example

For ColA value of 10, the value of ColC is 2

Now I need to go to the input file and among the rows with ColA value of 10, grab the last 2 rows and send them to output link and send the other rows to the reject link.

What is the best/efficient way to do this task?
Any Inputs will be greatly appreciated.

[chulett]

That's an... interesting requirement.

About the only advice I have off the top of my head would be to sort your input file on ColB descending so that you can take the first X rows that match the looked up value rather than the last. Last is generally a pain and involves reading ahead and also knowing when you are at the end of your data stream.

First would be simpler to handle and I would think stage variables would come in extremely handy here.

[pnchowdary]

Hi Craig,

Your solution is good. I was thinking along the same lines. However, I wanted to know the various options that I have, to tackle this problem, before choosing a method. Thank you very much for your input.