Incremental Lookup
Posted: Mon May 16, 2011 5:52 pm
I have two source files, one of which will be used as a Lookup file. I did not know what to call it so I named it the way I wish to use it, incremental lookup, but since it restarts from the top, so I know even incremental does not suit it, please let me know what is it called?
Lookup file has several duplicate matching values listed ("00" and "01" in the sample data below are those values) but different corresponding values that we need from Lookup file. Once both source, and Lookup files are sorted based on matching colum/key colum ("00"/"01" colum) , I should be able to match starting from top and pick the corresponding value and move down the list and get its corresponding value from the Lookup file. Once we reach to the end of matching value list in the Lookup file, and there is no more but source file still has some values to match, we should start from the beginning of the Lookup file list for matching values and get its corresponding values, until we run out of matching values in the Source file. It also means we will probably end up with several duplicate corresponding values in the Desired output file. I hope I am able to outline the pic. Here is the sample data, and desired output.
Please note, All data is in Char, and ASCII fixed length file.
Lookup File:
---------------
abc-00
def-00
uvw-01
xyz-01...
Main Source file:
--------------------
123R00
456R00
789R00
111R00
222R00
787R01
488R01
422R01
788R01...
Desire Output
-----------------
123R00abc
456R00def
789R00abc
111R00def
222R00abc
787R01uvw
488R01xyz
422R01uvw
788R01xyz...
Lookup file has several duplicate matching values listed ("00" and "01" in the sample data below are those values) but different corresponding values that we need from Lookup file. Once both source, and Lookup files are sorted based on matching colum/key colum ("00"/"01" colum) , I should be able to match starting from top and pick the corresponding value and move down the list and get its corresponding value from the Lookup file. Once we reach to the end of matching value list in the Lookup file, and there is no more but source file still has some values to match, we should start from the beginning of the Lookup file list for matching values and get its corresponding values, until we run out of matching values in the Source file. It also means we will probably end up with several duplicate corresponding values in the Desired output file. I hope I am able to outline the pic. Here is the sample data, and desired output.
Please note, All data is in Char, and ASCII fixed length file.
Lookup File:
---------------
abc-00
def-00
uvw-01
xyz-01...
Main Source file:
--------------------
123R00
456R00
789R00
111R00
222R00
787R01
488R01
422R01
788R01...
Desire Output
-----------------
123R00abc
456R00def
789R00abc
111R00def
222R00abc
787R01uvw
488R01xyz
422R01uvw
788R01xyz...