Six Stars

## Find value in range in different rows Need advice

Hello,

I'm a TOS and Java newbie.

My problem:

I have to CSV files

1) consumed quantitiy

Key  Quantity

aa    250

2) priceliste

Key Quantity price

aa   100        4.0

aa    200       3.5

aa    300       3.0

I have to find the price 3.5

The consumed quantity has to be in the range >= row1 and <= row2.

Do you know a solution. I'm working with TOS DI 6.3

Thanks

hu

3.0

Tags (1)

Accepted Solutions
Sixteen Stars

## Re: Find value in range in different rows Need advice

That is what the job does. You are supplying the quantity ranges on separate rows (from and to), what I am doing is demonstrating a way of dealing with that by using a strict ordering and then an aggregation. If you run the job you should see this. Take a look at the input values. I may have added too many "aa" inputs, but essentially this should meet your requirements.

All Replies
Sixteen Stars

## Re: Find value in range in different rows Need advice

Hi and welcome to the world of Talend :-)

First of all, I *think* your requirement is slightly incorrectly specified. You say "The consumed quantity has to be in the range >= row1 and <= row2". Looking at your data, it looks like you are trying to match prices against quantities. Most of the time these are as below....

0-100 = 4.0
101-200 = 3.5

201-300 = 3.0

301-400 = 2.0

I sense that this is what you mean.

If so, this example job should show you how to do it. I have used your data (with some extra) to demonstrate.

Essentially what I am doing here is matching on key and then assigning a boolean variable to say whether the quantity than or equal to the quantity bracket. All rows which match this pass to the next stage. I then sort the data by key, quantity purchased and quantity bracket. From there I use the tAggregateRow, group by key and quantity purchased and then return the last price returned. I have attached the job here. It was built using Talend 6.2.1.

There are some assumptions I have had to make from your description of the problem. I have also added some extra price brackets.

Six Stars

## Re: Find value in range in different rows Need advice

Hello,

thanks for your answer. But in one row a a product_id a quantity and a price. E.g.

Id   quantity price

aa     100     3.5

My problem is to find the price, which fits in the range of quantities  between 2 rows.

regs

Sixteen Stars

## Re: Find value in range in different rows Need advice

That is what the job does. You are supplying the quantity ranges on separate rows (from and to), what I am doing is demonstrating a way of dealing with that by using a strict ordering and then an aggregation. If you run the job you should see this. Take a look at the input values. I may have added too many "aa" inputs, but essentially this should meet your requirements.

Six Stars

## Re: Find value in range in different rows Need advice

Hi,

thanks a lot for your example. It solves my problem. Sorry, but my English is very poor.

regs

Sixteen Stars

## Re: Find value in range in different rows Need advice

Not a problem, your English is better than my ....any other language :-)