DSXchange

Posted: **Wed Jul 20, 2005 5:54 pm**

Hi Guys,

My Input file is of the below format

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.

Posted: **Wed Jul 20, 2005 5:59 pm**

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.

Posted: **Wed Jul 20, 2005 6:06 pm**

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.

DSXchange

Filtering Input Records in Sequential file

Filtering Input Records in Sequential file