Lookup

shrey3a · Post by **shrey3a** » Tue Oct 18, 2005 12:42 pm

Hi,

I've a scenario

Table A

Code Age
1 >25
2 25-50
3 50-75
4 <75

Table B

Age

25
23
45
67
46
55
89

I need to pick up the code from table A by comparing the age e.g. 55 fall in 50-75 so the lookup shuld pick up 3.

*table A is not static it keeps on changing
Thanks in advance

Regards

sjhouse · Post by **sjhouse** » Tue Oct 18, 2005 1:02 pm

For something like this with just 4 ranges that are considered static, I would either use a nested if statement in the derivation or create a routine using a case statement.

I like the routine, because you only need to modify 1 place if the ranges would change.

Hope this helps.

Stephen

shrey3a · Post by **shrey3a** » Tue Oct 18, 2005 2:11 pm

Its not static and ranges are more then 4 ....

Regards,
Munish

sjhouse wrote:For something like this with just 4 ranges that are considered static, I would either use a nested if statement in the derivation or create a routine using a case statement.

I like the routine, because you only need to modify 1 place if the ranges would change.

Hope this helps.

Stephen

I_Server_Whale · Post by **I_Server_Whale** » Tue Oct 18, 2005 3:20 pm

What exactly do you mean by "not static"??? Please give an example.

Are you saying that there can be more number of ranges ?

Or are you saying that the values of the range may change?

Thanks!

Naveen.

shrey3a · Post by **shrey3a** » Tue Oct 18, 2005 3:35 pm

Hi ,

its both values can also change and there are more number of ranges.

Rgds

[quote="naveendronavalli"]What exactly do you mean by "not static"??? Please give an example.

Are you saying that there can be more number of ranges ?

Or are you saying that the values of the range may change?

Thanks!

Naveen.[/quote]

ray.wurlod · Post by **ray.wurlod** » Tue Oct 18, 2005 5:10 pm

Pre-load a hashed file from Table A. The hashed file contains every possible age - they look like people's ages so there will only be 100 or so rows - and the corresponding code. This should load in well under a second. The rest is a simple lookup based on age. I've assumed here that age is an integer of some kind.

The more rows you have in Table B, the better this solution is.

DeepakCorning · Post by **DeepakCorning** » Wed Oct 19, 2005 11:59 am

We had the same requirement and We did what ray suggested in the previous annswer -->
Create a dn maintain a CSV or flat file with all the possible values in it.
In the load the HASH file from CSV file and do the lookup.

Doing it from CSV file is a better way (i think) as whenever the requirement changes like the range descr for 25 - 50 may sometime in future change to 25 - 35 and 35- 50 then it will be easie for you to change it directly in the CSV/Flat file.

logic · Post by **logic** » Wed Oct 19, 2005 12:51 pm

Hi Deepak,
I am sorry but I did'nt get it.

Why would it be better to load to a CSV file. Because even if there are changes in Table A, they would be transferred to the hash File as suggested by Ray!!!
Even if you are considering that when the business requirement changes then the changes have to be made Manually. Then would'nt the changes be made to table rather than making the changes into the intermediate file

thanks.