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

user_9164189 0
Viewed 38

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.

tmap talend
1 Answers

This is doable with a subjob , a bit complex but an interesting one : enter image description here 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 : enter image description here

  • 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.

user_65832040
Related