[resolved] Read through an entire table for each row of another table

[Anonymous]

Hello,
I'm quite new in Talend's word and, in front of what I thought would be an easy task, I'm stuck.
I'm using Talend Open Studio for Data Integration - Version 5.2.0M3 on a Debian machine.
Here is my issue :
I have two inputs :
-> events: it's a MySQL table filled with events. Each of them has a start date, an end date (in a timestamp format) and a duration (endDate - startDate).
-> holidays: It's a CSV file containing the holidays. The file has the following format : year | month | day (all fields are integers)
I want, for each row of the events table, see if there is some holidays between the start date and the end date.
My goal is that the variable duration (from the events table) has the exact amount of time between the start date and the end date without the time spent in the holidays.
I was thinking about using the tJavaflex component but I have no idea how to iterate on the holidays table for each row of the events table.
Thank you in advance for you answers,
--
Best regards
Bertrand

[Anonymous]

With tJavaflex you can achieve allmost anything, but for this I would try to use this:
tFileInputDelimited
\
tMap --> tFileOutputDelimited
/
tFileInputDelimited

I think wouldn't create a join as you do want to have a cartesian product (check all on all)
You have to create some advanced conditions on the output to check the dates. Create some and / or statements to make these matches.

[alevy · Accepted solution]

Like this.
Code in tJavaRow_1 is:
output_row.date = TalendDate.parseDate("yyyyMMdd",""+input_row.year+(input_row.month<10?"0":"")+input_row.month+(input_row.day<10?"0":"")+input_row.day);
Code in tJavaRow_2 is:
output_row.id = input_row.id;
output_row.start_date = input_row.start_date;
output_row.end_date = input_row.end_date;
output_row.duration = input_row.duration;
output_row.adjusted_duration = input_row.duration - input_row.total_holidays;

[Anonymous]

Hi Saukema and Alevy,
This is great ! It's exactly what I was looking for.
I didn't know that the tMap would create a new table with all the entries from the 2 input tables.
I was trying to do that directly in a tJavaFlex, which was giving me errors.
Thank you very much for your answers.
Have a good day !