topic Re: [resolved] How to select only maximum value from tMap? in Talend Studio

[resolved] How to select only maximum value from tMap?

Anonymous — Wed, 29 Mar 2017 08:56:12 GMT

Hi All,
I would like to select only the maximum value of num.
num column is located in table2.

Here is an sql example:
select max(num)
from table1 left join table2
on (condition1>condition2)

I have tried performing the above sql using tMap.
table1 --> tMap --> table3
table2(lookup)

I have no problem trying to do a left join of table1 and table2 and I manage to include the condition at the filter in tMap. However, I do not know how to get the max(num).
Table3 have an empty num column where I can input the max(num) into.
Please help.

Re: [resolved] How to select only maximum value from tMap?

TRF — Wed, 29 Mar 2017 09:11:55 GMT

Hi,
As tMap process records one at a time it is not the place to do that.
You may:
- get the max value into a global variable before entering into the tMap using SQL, then filtering with that variable
- select the values from SQL, then sort descending and process for the 1rst row only
Regards,
TRF

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Wed, 29 Mar 2017 09:15:56 GMT

@TRF Hello! thank you for replying. how do I make it to process for 1st row only?

Re: [resolved] How to select only maximum value from tMap?

TRF — Wed, 29 Mar 2017 09:25:37 GMT

just add a tSampleRow before the tMap with the folloging parameter "1"

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Wed, 29 Mar 2017 09:49:23 GMT

@TRF, I tried the tSampleRow but couldn't get the output required. This is an example of the table output required:

Thanks.

Re: [resolved] How to select only maximum value from tMap?

TRF — Wed, 29 Mar 2017 10:10:34 GMT

I don't understand your use case.
Can you be more explicit?

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Wed, 29 Mar 2017 10:32:15 GMT

@TRF, sure! (:
From table2,
We will be able to get the info that any dates from 2017-02-01 to 2017-03-01, num =11
and
any dates from 2017-03-02 to 2017-04-01, num =12
Therefore, for the output table3, we have to get the num from table2 for the dates in table 1.
e.g for 2017-02-15, num = 11
for 2017-03-15, num = 12

Re: [resolved] How to select only maximum value from tMap?

TRF — Wed, 29 Mar 2017 11:30:31 GMT

Well, a little bit more complicated than a simple tMap join as you expect a date comprised between 2 others (tMap join use equality).
First, you need to construct an intermediate table for table2 with the following values:
num startDate endDate
11 1900-01-01 2017-02-01
12 2017-02-01 2017-03-01
13 2017-03-01 2017-04-01
tSortRows and tMemorizeRows should help you for that.
Now, try this:
row2(newTable2)
|
row1(Table1) --> tMap --> the result
Add a filter to Table2 as the following:

row1.Date.compareTo(row2.startDate) > 0 && row1.Date.compareTo(row2.endDate) <= 0

It is the principle, I've not tried but should work (I hope so).
Regards,
TRF

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Thu, 30 Mar 2017 02:45:23 GMT

@TRF, Hello! Sorry for the late reply.
Table2 will be updated frequently(new rows will be added to it).
Is there a way to create the newTable2 such that it will always work just by clicking the run button without modifying anything when Table2 is updated?

Re: [resolved] How to select only maximum value from tMap?

TRF — Thu, 30 Mar 2017 09:17:39 GMT

Hi,
You don't have to create and fill a new table. newTable2 is a tHash which is an in memory object on Talend side.
Using a tJavaRow you can complete this newTable2 like this:

output_row.num = input_row.num;
output_row.endDate = input_row.date;
output_row.startDate = ((String)globalMap.get("startDate"));
globalMap.put("startDate", input_row.date);

Global variable "startDate" is initialized with "1900-01-01" when job starts.

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Thu, 30 Mar 2017 10:21:55 GMT

@TRF, It's working! Thanks a lot for your explanation once again! (:

Re: [resolved] How to select only maximum value from tMap?

TRF — Thu, 30 Mar 2017 11:17:02 GMT

Tell what is your database (oracle? mySQL ? postgreSQL ? other ?)
One way should be to convert from date to string, or better to integer, when selecting from table.
You tSetGlobalVar to initialize a global variable.

Re: [resolved] How to select only maximum value from tMap?

Anonymous — Thu, 30 Mar 2017 11:19:44 GMT

@TRF, I manage to get it work with your working.
Just that I used context.date instead of globalMap. I made some mistake in the code for context.date and that is why there is error message. But it's working now! Thanks!

Re: [resolved] How to select only maximum value from tMap?

TRF — Thu, 30 Mar 2017 11:45:11 GMT

You're welcome