TALEND STUDIO - Get number of Non-working Days between start_date and end_date using TMAP in Talend

Question

I am a beginner in Talend and I need to achieve the following:

For example, I have two input tables in the TMAP component.

Table 1:

Start_Date	End_Date
25/8/2022	1/9/2022

Table 2 (Lookup Table):

Non_working_days	Remark
27/8/2022	Weekend
28/8/2022	Weekend
31/8/2022	Weekend

I would want my output to count the number of non-working days from the lookup table.

For exp:

Start_Date	End_Date	No_of_non_working_days
25/8/2022	1/9/2022	3

Can this be achieved by using the expression editor in the TMAP component or I will need to create a routine to achieve it?

Thanks.

Answer 1

This is doable with a subjob , a bit complex but an interesting one : Main idea: generate all dates between startDate and endDate, then compare each one of these dates to the content of table 2. Then count the number of corresponding dates.

• tFixedFlow1 (table 1) place here your input table 1

• tFlowToIterate : this will create global variables for startDate and endDate, that will be important for the next steps

• tLoop : the aim is to generate all dates contained between startDate and endDate See detail :

• tIterateToFlow : once we have created all dates between start and endDate, regroupe the iterationFlow into a unique flow.

• tLogRow : just so you can control content.

• tMap+table 2 : join input flow with lookup from your table 2. Make it an innner join.

• tAggregate : count the number of lines in the output

• tLogRow : print screen of the result.