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

[Anonymous]

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.

[TRF]

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

[Anonymous]

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

[TRF]

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

[Anonymous]

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

[TRF]

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

[Anonymous]

@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

[TRF · Accepted solution]

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

[Anonymous]

@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?

[TRF]

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.