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)
1 ACCEPTED SOLUTION

Accepted Solutions
Twelve 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.

Rilhia Solutions
5 REPLIES
Twelve 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.  

Rilhia Solutions
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

Twelve 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.

Rilhia Solutions
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


Twelve Stars

Re: Find value in range in different rows Need advice

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

Rilhia Solutions