One Star

MIN or MAX from a set of Date Variables

Hi,
I use TOS3.1.2 (JAVA), does anyone know how I can extract the joungest and latest date from a set of dates. Format date is : yyyymmdd.
First I transferred the string variabele datum to a date with an expression in tMAP:
TalendDate.parseDate("yyyyMMdd",row1.Datum)
I want to go a step further and extract only the youngest and latest date.
Regards,
Michel
7 REPLIES
Community Manager

Re: MIN or MAX from a set of Date Variables

Hello
You can use the tAggregateRow component with min or max function.
eg:

20090212
20090313
20081202
20081213

Result:
Starting job forum7316 at 13:37 10/07/2009.
.--+--------.
| tLogRow_1 |
|=-+-------=|
|id|date |
|=-+-------=|
|1 |20081202|
'--+--------'
.--+--------.
| tLogRow_2 |
|=-+-------=|
|id|date |
|=-+-------=|
|1 |20090313|
'--+--------'
Job forum7316 ended at 13:37 10/07/2009.

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MIN or MAX from a set of Date Variables

Hi Shong,
i really do appreciate your reply, but is there probably an other way to extract the MAX and MIN out of a set of variables? In the tMAP component for example? I will adapt your solution but honestly I had expected to find the solution in the expression area of the tMAP component.
Kind Regards,
Michel
Community Manager

Re: MIN or MAX from a set of Date Variables

Hello
but is there probably an other way to extract the MAX and MIN out of a set of variables? In the tMAP component for example?

I get the max/min date on tJavaRow and set them to global vars which can be used later.
eg:
in.csv:

20090212
20090313
20081202
20081213

tJavaRow:
if(input_row.date!=null){
//max date
if(context.maxDate==null){
context.maxDate= input_row.date;
}else{
if(context.maxDate.getTime()<input_row.date.getTime()){
context.maxDate=input_row.date;
}
}
//min date
if(context.minDate==null){
context.minDate= input_row.date;
}else{
if(context.minDate.getTime()>input_row.date.getTime()){
context.minDate=input_row.date;
}
}
}
output_row.date=input_row.date;
output_row.maxDate=context.maxDate;
output_row.minDate=context.minDate;
//you can aslo set the max/min date to global vars which will be used //later. eg:
globalMap.put("maxDate",context.maxDate);
globalMap.put("minDate",context.minDate);

Result:
Starting job forum7316 at 17:50 11/07/2009.
.--------+--------+--------.
| tLogRow_1 |
|=-------+--------+-------=|
|date |maxDate |minDate |
|=-------+--------+-------=|
|20090212|20090212|20090212|
|20090313|20090313|20090212|
|20081202|20090313|20081202|
|20081213|20090313|20081202|
'--------+--------+--------'
Job forum7316 ended at 17:50 11/07/2009.

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MIN or MAX from a set of Date Variables

Hi Shong,
thanks a lot, regarding your solution (especially the last one) helps me a step furter in order to fill global variables dynamically.
Regards,
Michel Coppoolse
One Star

Re: MIN or MAX from a set of Date Variables

Morning Shong,
instead of what I suggested in my yesterday submitted message, I don't understand how you can resolve a global variable in a new variable. I'm using TIS/Java and what I've done is the following:
- tAggregate_row (here I created a minumum and maximum date)
- tReplicate_1 (in order to put the min and max date in an csv file - it contains just one row)
- tJavaRow (here I want to put the both date variable in two global variables minDate and maxDate in order to use them in following jobs)
- tLogRow (in the log I want to check whether the content of the global variables are correct)
In the above mentioned situation the output in de log is 'null' for the two global variables, but I do now that after the replicate there are two values (min and max date). I check that in the .csv file. I quess the resolving proces doesn't go right. See below the script I use in the tJavaRow and several screenshots.

row5.MaxDate=context.maxDate;
row5.MinDate=context.minDate;
//Set the max/min date to global vars which will be used //later. eg:
globalMap.put("MaxDate",context.maxDate);
globalMap.put("MinDate",context.minDate);
System.out.println((String)globalMap.get("MaxDate"));
System.out.println((String)globalMap.get("MinDate"));
Hope to hear from you soon.
Best Regards,
Michel Coppoolse
One Star

Re: MIN or MAX from a set of Date Variables

Does anyone have any idea?
Regards,
Michel
Community Manager

Re: MIN or MAX from a set of Date Variables

Hello Michel
You don't set a value to context vars, so you aslo get null values. You can see I set a value to context var on my previous post:
//max date
if(context.maxDate==null){
context.maxDate= input_row.date;
}else{
if(context.maxDate.getTime()<input_row.date.getTime()){
context.maxDate=input_row.date; //here I set a value to context var.
}
}
//min date
if(context.minDate==null){
context.minDate= input_row.date;
}else{
if(context.minDate.getTime()>input_row.date.getTime()){
context.minDate=input_row.date; //here I set a value to context var
}
}
}

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business