I have a series of transformations and joins in a Talend OS job (from MySQL connection) which I need to perform a date calculation on.
I currently have a table of the merges etc. which I'll refer to as 'test':
(Note: date format is just for pure example - they are in mySQL format in the tables)
Date | ReplyDate | NoOfDays
---------------------------------------------
2016-01-01 2016-01-07 6
2016-01-06 2016-01-11 5
...is an example of the key fields involved. Talend's diffDate calc can and has created a 'Number of Days' field, but it doesn't allow for non-working days (which is a key calculation on the field).
I have created a separate table, called 'NWD' (non working days) which is simply...
NWD
-----------
2016-01-01
2016-01-02
2016-01-03
2016-01-09
2016-01-10
...etc, and lists all public/bank holidays as well as weekends.
In SQL, I know I could do:
SELECT COUNT(nwd) from NWD where NWD >= [Date] and NWD <= [ReplyDate]
..but I'm struggling to get my head around how I could generate this calculation for each row at either stages. I'm not sure if this is something that should be handled by Talend, or a calculated field in Tableau somewho.
I'm a bit new to both, but I have tried to look at intricate joins with a tMap and tIntervalMatch in Talend, but it seems to be out of scope. I have exported both 'tables' into a localhost MySQL so Tableau can see both datasets, but I am a bit confused as to how I would perhaps 'join' anything here.
Am I:
a) being daft and green; b) Barking up the wrong tree with both paths, or; c) In over my head?
Can anyone advise/guide me? I have searched for a similar conundrum, but struggled to find anything quite in this context, although it must be a common issue? I'm not savvy enough with Java or Talend OS at this stage to be able to create and utilise a class.
Thanks in advance.
To conclude this one, I performed a join in Talend with my CSV file (which contained a cumulative count of working days over a 4 year period), adding a field for every row based on the relevant date of that row. I did this for my 'start' and 'end' dates that already existed in the data.
In Tableau, I used these lookup fields (passed over as a static 'value' which came from the CSV) to create a calculated field.
i.e. (made up example - in the UK, the 29 Aug is a Bank Holiday)
| S_DATE | E_DATE | S_DATE_VAL | E_DATE_VAL |
------------------------------------------------
| 25/08/16 | 30/08/16 | 1025 | 1027 |
In Tableau, simply (E_DATE_VAL) - (S_DATE_VAL) gave me my working days value.