topic Re: Find value in range in different rows Need advice in Talend Studio

Find value in range in different rows Need advice

husibilski — Sat, 16 Nov 2024 09:20:28 GMT

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

3.0

Re: Find value in range in different rows Need advice

Anonymous — Sun, 03 Sep 2017 00:07:05 GMT

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.

PriceRange.zip

Re: Find value in range in different rows Need advice

husibilski — Sun, 03 Sep 2017 11:39:42 GMT

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

Re: Find value in range in different rows Need advice

Anonymous — Sun, 03 Sep 2017 13:07:52 GMT

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.

Re: Find value in range in different rows Need advice

husibilski — Sun, 03 Sep 2017 14:03:41 GMT

Hi,

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

regs

Re: Find value in range in different rows Need advice

Anonymous — Sun, 03 Sep 2017 14:06:22 GMT

Not a problem, your English is better than my ....any other language 🙂