Get all dates between startdate and end date

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

All Replies
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
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

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.

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
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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Talend Cloud Developer Series – Defining Metadata

This video focuses on different methods of adding metadata to a job in Talend Cloud

Watch Now