increment the date values with in range & cumulative sum

[karthik_T]

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

[Anonymous]

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

[karthik_T]

Hi  @nthampi

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

[Anonymous · Accepted solution]

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

[akumar2301]

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

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

[karthik_T]

Hi  @nthampi,

Please explain the exp mentioned in attachment.

exp.png

[Anonymous]

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 🙂