topic Re: increment the date values with in range & cumulative sum in Talend Studio

increment the date values with in range & cumulative sum

karthik_T — Fri, 22 Feb 2019 05:48:05 GMT

Hi All,

I am trying to increment the date values with in range. can any one tell me how to achieve it

Please find the attached doc for input and output

Re: increment the date values with in range & cumulative sum

Anonymous — Fri, 22 Feb 2019 06:10:10 GMT

@karthik_T

Why is the cumsum column is 0 between 11-11-2019 and 14-11-2019? I guess it should be 1 for those dates also.

Please confirm.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: increment the date values with in range & cumulative sum

karthik_T — Fri, 22 Feb 2019 06:27:34 GMT

Hi @nthampi

Yes,i got the cumulative sum result & now i want to increment the date values as shown in output.

Re: increment the date values with in range & cumulative sum

Anonymous — Fri, 22 Feb 2019 10:14:16 GMT

Hi,

I am leaving the Csum to you as you know it. The objective is to fill the missing dates by referring to a calendar and it is as shown below.

The first stage is to delete the file where you want to store the calendar

Read the file and find the min and max date for each id.

Then send it to a tFLowtoIterate for iteration (to generate the dates between minimum and maximum calendar date for each id in iterative fashion)

The real magic is happening in trowgenerator.

Please use the following values for each entry in trowgenerator.

id value - Use the value from tFlowtoIterate

((String)globalMap.get("row2.id")) 

date value - Select Talend Date Add date function as shown in screen shot (exactly same function as there are multiple options for it)

Number of rows in tRowgnerator - Use Talend Diff date function to get the difference date between min and max dates. 
The output will be in Long. Convert to integer by adding (int) at beginning and then add +1 to get last entry

 (int)TalendDate.diffDate(((java.util.Date)globalMap.get("row2.max_date")),((java.util.Date)globalMap.get("row2.min_date")),"dd")+1

At parameter side, please provide below values

date -     ((java.util.Date)globalMap.get("row2.min_date"))              (from tFlowtoIterate min date)
nb    -     Numeric.sequence(((String)globalMap.get("row2.id")),0,1)      (note:- the sequence will repeat for each incoming id from tFlowtoiterate)
datetype - "dd"

Load the data to a calendar file in append mode

In second subjob, calendar will be the mainflow and your input file will be input file

I am attaching the job also (version 7.1 of Talend Data Fabric used for job creation) for reference.

Hope I answered your query. Before going, could you please mark the topic as resolved? Kudos will be bonus 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

testjob.zip

Re: increment the date values with in range & cumulative sum

akumar2301 — Fri, 22 Feb 2019 13:53:08 GMT

Below two properties of tMap could be useful

1) tMap memorize previous run record

https://community.talend.com/t5/How-Tos-and-Best-Practices/Compare-row-value-against-a-value-from-the-previous-row/m-p/133710#M595

2) tMap ( Reload on each record ) could be used a row Multiplier

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

Algorithm

1) get how many time each row should repeated . ( For this you could sort input in descending order of Datevalue and Date difference from Previous Datevalue )

result should be like

102|20-11-2019|2|1

102|16-11-2019|0|4
101|15-11-2019|0|1
101|10-11-2019|1|5

2) use row Multiplier logic , and repeat same row n number of time. Also add 1 for each Iteration.

101|10-11-2019|1
101|11-11-2019|0
101|12-11-2019|0
101|13-11-2019|0
101|14-11-2019|0
101|15-11-2019|0
102|16-11-2019|0
102|17-11-2019|0
102|18-11-2019|0
102|19-11-2019|0

102|20-11-2019|2

simplejob_0.1.zip

Re: increment the date values with in range & cumulative sum

karthik_T — Mon, 25 Feb 2019 07:01:14 GMT

Hi @nthampi,

Please explain the exp mentioned in attachment.

exp.png

Re: increment the date values with in range & cumulative sum

Anonymous — Mon, 25 Feb 2019 11:14:04 GMT

Hi,

The id will receive the value from tFlowtoiterate directly.

The date will have the minimum value and I have added a numeric sequence to add the dates from minimum date. This numeric sequence will add 1 to minimum date in sequential fashion to generate all dates.

The number of records will take the difference of maximum and minimum date+1. This will determine the number for records to be generated for each id.

The details at the bottom part will have the split up of Talend date function.The date column will store the starting date (min date in this case) and the nb will store the number to add to minimum date. Since we have added sequence function, the value will get incremented for each record.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂