From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

Data Integration CSV File - Populate a cell with previous rows value for that cell

Highlighted
Five Stars

Data Integration CSV File - Populate a cell with previous rows value for that cell

Hey folks,

 

Newbie to Talend here and getting stuck with processing a CSV file. Basically I want to check a row in my CSV file and if the data in a cell is empty I wish to populate it with the value from the previous rows. example of rows below. So in the example below for the Year in the 4th row I'd like this to be 2000 etc.

 

I was trying to use a "tjavarow" function but got completely lost any help is much appreciated.

 

Neil


Accepted Solutions
Highlighted
Employee

Re: Data Integration CSV File - Populate a cell with previous rows value for that cell

Hi,

 

     Your approach to use tjavarow is a right one. You need to make only minor change. You need to assign the value to a context variable also so that you can reuse it for next iteration. A sample pseudo code is as shown below.

Step 1:-

assign empty string or null to variable Year in context section. This is to handle the value as null for first row.

 

Step 2:-

In tjavarow, do the below logic 

 

if (input_row.Year== null)
{
output_row.Year=context.year;    // which will pick the value in context instead of input record
}
else
{

output_row.Year=input_row.Year;
}

context.year=input_row.Year; //update value of context which will be used for next row

 

Hope the idea has answered your query. Could you please mark the topic as resolved if you are happy? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

View solution in original post


All Replies
Highlighted
Five Stars

Re: Data Integration CSV File - Populate a cell with previous rows value for that cell

MovieID Year Title Publisher Superheroes Noofsuperheroes RTscoreper Distributor 
3            2000 X-Men Marvel Xavier 13 81% 20th Century Fox 7/14/00 75 296339527
4                     X-Men Marvel Wolverine 13 81%

Highlighted
Employee

Re: Data Integration CSV File - Populate a cell with previous rows value for that cell

Hi,

 

     Your approach to use tjavarow is a right one. You need to make only minor change. You need to assign the value to a context variable also so that you can reuse it for next iteration. A sample pseudo code is as shown below.

Step 1:-

assign empty string or null to variable Year in context section. This is to handle the value as null for first row.

 

Step 2:-

In tjavarow, do the below logic 

 

if (input_row.Year== null)
{
output_row.Year=context.year;    // which will pick the value in context instead of input record
}
else
{

output_row.Year=input_row.Year;
}

context.year=input_row.Year; //update value of context which will be used for next row

 

Hope the idea has answered your query. Could you please mark the topic as resolved if you are happy? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

View solution in original post

Highlighted
Five Stars

Re: Data Integration CSV File - Populate a cell with previous rows value for that cell

Thank you Nikhil for the help it was much appreciated and solved my problem once I learned about context variables! Thanks again

Neil.

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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

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