lookup on variable keys
Posted: Tue Feb 14, 2006 6:09 am
hi,
Please help me out.
i have a reference file of the following type.
1st_char f1 f2 f3 f4 f5 f6 f7 prod_code
D - - - - - N - RB
D - - - - - - - RD
A - - - - - - Y RJ
A N Y - N - - - RH
A - Y - - N Y - RF
W - - - N - N Y RK
.
.
.
there is no unique key. based on the 1st char & the values of the flags, the prod_code is to be populated. "-" implies that the flag's value does not matter. it can be anything Y/N.
I have a primary file which contains all the above fields & in which prod_code is to be populated.
Both files are large.
Currently we are hardcoding the flag values to populate the prod_code using if-else statements.
i want to make this configurable to avoid code changes when new prod_codes are added.
a simple lookup wont work because we dont know which keys to lookup on.
if join is performed then there will be n.m iterations where n=no.of rows in primary file & m=no. of rows in reference file which will turn into millions of records.
Can anyone suggest an efficient solution for this.
To simplify-the problem is to perform lookup on variable keys.
Thanks & Regards
r
Please help me out.
i have a reference file of the following type.
1st_char f1 f2 f3 f4 f5 f6 f7 prod_code
D - - - - - N - RB
D - - - - - - - RD
A - - - - - - Y RJ
A N Y - N - - - RH
A - Y - - N Y - RF
W - - - N - N Y RK
.
.
.
there is no unique key. based on the 1st char & the values of the flags, the prod_code is to be populated. "-" implies that the flag's value does not matter. it can be anything Y/N.
I have a primary file which contains all the above fields & in which prod_code is to be populated.
Both files are large.
Currently we are hardcoding the flag values to populate the prod_code using if-else statements.
i want to make this configurable to avoid code changes when new prod_codes are added.
a simple lookup wont work because we dont know which keys to lookup on.
if join is performed then there will be n.m iterations where n=no.of rows in primary file & m=no. of rows in reference file which will turn into millions of records.
Can anyone suggest an efficient solution for this.
To simplify-the problem is to perform lookup on variable keys.
Thanks & Regards
r