topic Re: Get all dates between startdate and end date in Talend Studio

Get all dates between startdate and end date

Anonymous — Sat, 16 Nov 2024 05:58:22 GMT

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.

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.

Re: Get all dates between startdate and end date

akumar2301 — Mon, 29 Apr 2019 16:01:29 GMT

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

Re: Get all dates between startdate and end date

Anonymous — Mon, 29 Apr 2019 16:20:52 GMT

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:

Re: Get all dates between startdate and end date

Anonymous — Mon, 29 Apr 2019 20:28:32 GMT

@uganesh 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.

Re: Get all dates between startdate and end date

akumar2301 — Mon, 29 Apr 2019 23:07:25 GMT

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

Re: Get all dates between startdate and end date

Anonymous — Tue, 30 Apr 2019 01:16:55 GMT

@uganesh Thanks Abhishek...

Kudos+++

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

That worked like a charm. Amazing.