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