One Star

Lookup based on a value range

Hello, I'm new to Talend, and I was wondering how to perform a join from a value to a value range. For instance:
I have a source table with a column "amount" with the following values:
1
2
54
220
I have a lookup table defined with the following columns

ID high low
1 15 0
2 50 16
3 100 51
4 500 101

I wanted to perform a lookup using a tmap object, mapping amount to a specific id so the results should yield:
1
1
3
4
Is this possible?
Any help would be appreciated.
Thanks and regards,
-Frank
12 REPLIES
Moderator

Re: Lookup based on a value range

Hello Franck,
tIntervalMatch does exactly that.
Please refer to the documentation on tIntervalMatch and its sample scenario
regards,
Benjamin
One Star

Re: Lookup based on a value range

Hah!
I just found it and was about to reply when I saw the message. I appreciate the reply
Thanks,
-Frank
One Star

Re: Lookup based on a value range

Maybe someone here can help. I have a similar problem, but I have 2 lookup fields to deal with. So I need to lookup based on 2 ranges. The tIntervalMatch only handles 1 lookup field. Has anyone had to do this before?
One Star

Re: Lookup based on a value range

with two successive tIntervalMatch
One Star

Re: Lookup based on a value range

I guess I wasn't clear. The lookup fields are on the same table. so I wanted to do a lookup on a table with 5 columns. The first columns is an ID, and the other four are two HIGH limit and LOW limit columns for different values. This means that the ID is based on if the lookup of the first value falls between the the first set of High-lows AND the lookup for the second value falls between the second set of high-lows.
One Star

Re: Lookup based on a value range

I don't really understand how your lookup file is. But I think that a possible solution is to split your file in two flow with a tMap.
Can you give me the first line of your two files (main and lookup)?
One Star

Re: Lookup based on a value range

Okay to use the example above,
I have a source table with a column "amount", and a column "rate" with the following values:
amount rate
1 .50
2 .80
54 .40
220 .90
I have a lookup table defined with the following columns. Row 5 is the value I would like to assign if the lookup fails. (I understand that this may need to be done in a separate step)
ID high_1 low_1 high_2 low_2
1 15 0 1 .90
2 50 16 1 .80
3 100 51 1 .70
4 500 101 1 .60
5 -1 -1 -1 -1.0
I wanted to perform a lookup where the results should yield:
5
2
5
4
Is this possible, or will I need to do some custom work?
One Star

Re: Lookup based on a value range

I don't understand the result you need.
You want to check the amount and the rate? In which order?
Because in your result you seem to use only the second part (high_2,low_2) to check only the rate
One Star

Re: Lookup based on a value range

ok I understood your problem
Maybe you can use a tfilter to check if the rate value is between 0.6 and 1.
Flow filter (the output of your tFilter)>you can use a tIntervalMatch on the amount value.
Flow rejet (the output of your tFilter)> you can put the value to 5.
One Star

Re: Lookup based on a value range

My Apologies. Yes, the second value should be 5, not 2. I pasted the wrong text. This should result in:
5
5
5
4
One Star

Re: Lookup based on a value range

ok I will try to do it, wait a moment please
One Star

Re: Lookup based on a value range

I think the main difficulty doesn't come from your double file but from the fact that your rate interval isn't really an interval.
I'm not able to do it at this moment. I'm sorry