topic Re: Lookup based on a value range in Talend Studio

Lookup based on a value range

_AnonymousUser — Sat, 16 Nov 2024 14:26:48 GMT

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

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 01:24:40 GMT

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

Re: Lookup based on a value range

_AnonymousUser — Tue, 04 Mar 2008 01:26:50 GMT

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

Re: Lookup based on a value range

_AnonymousUser — Tue, 04 Mar 2008 16:50:10 GMT

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?

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 17:05:27 GMT

with two successive tIntervalMatch

Re: Lookup based on a value range

_AnonymousUser — Tue, 04 Mar 2008 17:15:48 GMT

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.

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 17:36:39 GMT

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)?

Re: Lookup based on a value range

_AnonymousUser — Tue, 04 Mar 2008 17:48:01 GMT

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?

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 18:05:09 GMT

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

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 18:14:06 GMT

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.

Re: Lookup based on a value range

_AnonymousUser — Tue, 04 Mar 2008 18:14:28 GMT

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

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 18:20:27 GMT

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

Re: Lookup based on a value range

Anonymous — Tue, 04 Mar 2008 18:41:41 GMT

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