increment the date values with in range & cumulative sum

Five Stars

increment the date values with in range & cumulative sum

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

 

 


Accepted Solutions
Employee

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

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.

image.png

 

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

image.png

 

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

image.png

 

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.

image.png

 

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 modeimage.png

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

image.png

 

 

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


All Replies
Employee

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

@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 :-)

Five Stars

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

Hi  nikhilthampi,

 

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

Tags (1)
Employee

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

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.

image.png

 

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

image.png

 

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

image.png

 

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.

image.png

 

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 modeimage.png

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

image.png

 

 

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

Eleven Stars

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

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

 

TalendImage.JPG

 

Regards
Abhishek KUMAR
Highlighted
Five Stars

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

Hi  nikhilthampi,

 

Please explain the exp mentioned in attachment.

 

 

Employee

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

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

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download