Generate/Filter dates in a row according to multiple range of dates

[AmIanALien0x3f]

Hello

I'm working on table containing date information for a datawarehouse, it is a "dimension date"

beyond standard date informations I would to add specific periode/range of date and process them directly at generation or in a second step

So one side I have a list of rows for all the date between -5 years and +5 years and on the other side I have a list of date ranges with et begin/end date

How I can process to complete the final rows with all my date -5/+5 years with the additionnal information of "this date is in one of the range of date contains in the second component" ?

Thank you in advance

RV

[Anonymous]

Hi

You can use built in function TalendDate.compareDate((Date date1, Date date2)) to compare two date if needed, then filter the data or add extra information in a new column. It's better to provide an example of your data and let us know what are your expected output.

Regards

Shong

[AmIanALien0x3f]

I don't know what the best to share example so I try by screenshot

Here you can find a simple exemple

• one branch to generate and complete all data linked to dates between -5/+5 years line by line
• another branch to store date range debut/fin of periode of each holiday
• a tMap that should add a column isHoliday as soon as a generated date row is in the range of one of the multiple line defining holiday range. It is "is in one of the multiple range" because ranges can overlap

 

[gjeremy1617088143]

HI maybe you can try a tIntervalMatch component :

https://help.talend.com/r/en-US/8.0/standardization/tintervalmatch-standard-properties

https://help.talend.com/r/en-US/8.0/standardization/tintervalmatch-tfileinputdelimited-tlogrow-tlogrow-identifying-server-locations-based-on-their-ip-addresses-standard-component-this

Send me love and kudos

[AmIanALien0x3f · Accepted solution]

Ok. Great it is exactly what I was looking for. Thank you

So I had to convert string dates to Date format

I add to add a column "isHoliday" set to "true" to holiday input range table to give the information of what to "say" when a date is in an interval of holiday dates

Add a tInterval, select all dates as SearchInput

Add the isHoliday component as LookUp input

Select isHoliday.debut and isHoliday.fin as limits

Select isHoliday.isHoliday as LOOKUP value to add when the date is in a range

Here you can find the job

For information to all users

I met a bug of this component. Checking "Include the Bound (max)" doesn't do anything and le max bound is still rejected of the test as it was not checked

In my component to generate date list, I met another bug with TalendDate.diffDate(Date1, Date2, "dd"). This function gives 0 of difference when Date1 and Date2 are equal (expected) but when Date1 is Date2-1 day (not expected), as "31-12-2021" and "01-01-2022" where equals...

I'll check if it is already reported and report it if needed

Thank you for support !