Get all dates between startdate and end date

Highlighted
Six Stars

Get all dates between startdate and end date

Hello All,

 

I am completely new to talend.

 

I am doing few pocs by connecting to mongodb, mysql, ms sql, sap, hana, excel.

I did succeeded by connecting through all the data sources and getting data and transmission.

 

I was struck at one place where i need to get the dates between start date and end date.

My Table Looks like as below.

 

|  EndDate   | StartDate  |
|=-------------+--------------+
|29-04-2019|025-04-2019|

 

I did calculated the difference between these two days using tMap.

My Workflow looks as below image.

Screen Shot 2019-04-29 at 7.33.14 PM.pngScreen Shot 2019-04-29 at 7.33.06 PM.pngScreen Shot 2019-04-29 at 7.33.31 PM.png

 

I would like to have my table output as

|  EndDate   | StartDate  | Date |
|=-------------+---------------+--------+
|29-04-2019|25-04-2019|25-04-2019

|29-04-2019|25-04-2019|26-04-2019

|29-04-2019|25-04-2019|27-04-2019

|29-04-2019|25-04-2019|28-04-2019

|29-04-2019|25-04-2019|29-04-2019

 

Can anyone please guide me how i can achieve this.

 

Thanks in Advance.

Mohan V.


Accepted Solutions
Highlighted
Eleven Stars

Re: Get all dates between startdate and end date

if is confusing , lets do another way

 

input -- tjavarow -- tflowtoiterate -- tjavaflex -- tlogrow

 

tJavarow - add aditional column diff ( int type)

 

output_row.end = input_row.end;
output_row.start = input_row.start;
output_row.diff= (int)TalendDate.diffDate(input_row.end, input_row.start,"dd");

---------------------

tJavaFlex -- repleate row diff number of time

 

start

for ( int i=0; i <= ((Integer)globalMap.get("row7.diff"));i++)
{

 

Main ( all colored row links need to be modified as per your job)

 

row8.end = ((java.util.Date)globalMap.get("row7.end"));
row8.start = ((java.util.Date)globalMap.get("row7.start"));
row8.date= TalendDate.addDate(row8.start,i,"dd");

 

end

 

}

-----------------------------

 

output

 

[statistics] connecting to socket on port 3509
[statistics] connected
29-04-2019|25-04-2019|25-04-2019
29-04-2019|25-04-2019|26-04-2019
29-04-2019|25-04-2019|27-04-2019
29-04-2019|25-04-2019|28-04-2019
29-04-2019|25-04-2019|29-04-2019
[statistics] disconnected

 

 

 

Regards
Abhishek KUMAR

View solution in original post


All Replies
Highlighted
Eleven Stars

Re: Get all dates between startdate and end date

Step 1 ) use tJava and add additional column

repeatRow which will have difference of two dates

and id = 1

 

//Code generated according to input schema and output schema

output_row.id = 1;
output_row.start_date = input_row.start_date;
output_row.end_date = input_row.end_date;
output_row.repeatRow= TalendDate.diffDate(input_row.end_date, input_row.start_date,"dd");

 

Step 2)  use row multiplier logic as explain in below link

 

https://community.talend.com/t5/How-Tos-and-Best-Practices/Row-Multiplication/m-p/39776#M1

 

 

You have to do little Modification in tMap Output side

 

Expression for Third Column "date" should be 

TalendDate.addDate(rowxxx.start_date,Numeric.sequence("s1",1,1),"dd")

 

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: Get all dates between startdate and end date

hello,

 

I use the following expression TalendDate.diffDateFloor(row3.FirstDate,row3.SecondDate,"MM") it worked to me, the only thing I do is format it to integer or double.

look at the image:

 

datediff.PNG

Highlighted
Six Stars

Re: Get all dates between startdate and end date

@akumar2301 thanks for the reply.

But seriously, i didn't understood how exactly it should be implemented.

Could you please elaborate more.

That will be very much appreciated.

 

Thanks,

Mohan V.

Highlighted
Eleven Stars

Re: Get all dates between startdate and end date

if is confusing , lets do another way

 

input -- tjavarow -- tflowtoiterate -- tjavaflex -- tlogrow

 

tJavarow - add aditional column diff ( int type)

 

output_row.end = input_row.end;
output_row.start = input_row.start;
output_row.diff= (int)TalendDate.diffDate(input_row.end, input_row.start,"dd");

---------------------

tJavaFlex -- repleate row diff number of time

 

start

for ( int i=0; i <= ((Integer)globalMap.get("row7.diff"));i++)
{

 

Main ( all colored row links need to be modified as per your job)

 

row8.end = ((java.util.Date)globalMap.get("row7.end"));
row8.start = ((java.util.Date)globalMap.get("row7.start"));
row8.date= TalendDate.addDate(row8.start,i,"dd");

 

end

 

}

-----------------------------

 

output

 

[statistics] connecting to socket on port 3509
[statistics] connected
29-04-2019|25-04-2019|25-04-2019
29-04-2019|25-04-2019|26-04-2019
29-04-2019|25-04-2019|27-04-2019
29-04-2019|25-04-2019|28-04-2019
29-04-2019|25-04-2019|29-04-2019
[statistics] disconnected

 

 

 

Regards
Abhishek KUMAR

View solution in original post

Highlighted
Six Stars

Re: Get all dates between startdate and end date

@akumar2301 Thanks Abhishek...

Kudos+++

Sorry, but i can give only one kudos at a time here Smiley Happy

That worked like a charm. Amazing.Heart

 

 

 

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Talend Cloud Available on Microsoft Azure

An integration platform-as-a-serviceto help enterprises collect, govern, transform, and share data from any data sources

Watch Now

Self-service Talend Migration: Moving from On-Premises to the Cloud

Move from On-Premises to the Cloud by following the advice of experts

Read Now

How to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud reg...

Learn how to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud registries

Blog