Update previous rows in tMap or tJava

Seven Stars

Update previous rows in tMap or tJava

Hi All,

I need one help to build below logic, we can use tMap or tJava.
My input and output is text file.

I want to populate Missing data using same value as first available value as per below screenshot.

 


One.jpg

 

above lookup can be used if required
Also Flag = "Y" can be used (denoted manually updated)

 

 

 

dataset

Acc_no|Period|Value|Flag
101|201601||Y
101|201602||Y
101|201603||Y
101|201604|30|N
101|201605|35|N
101|201606|35|Y
101|201607|35|Y
201|201601||Y
201|201602||Y
201|201603||Y
201|201604|33|N
201|201605|44|N
201|201606|44|Y
201|201607|44|Y

 

 

Thanks,
Sachin

 

Tags (1)

Accepted Solutions
Community Manager

Re: Update previous rows in tMap or tJava

Hi
First, filter the null value and then do aggregation using tAggregateRow to get the first value of each Acc_no, output the value and put it to global variable for each Acc_no, the job looks like:
tfileinputdelimited1--tfilterRow--tAggregateRow--tJavaRow
|
onsubjobok
|
tfileinutDelimited2-row4-->tMap---tfileoutputDelimited
on tJavaRow:
globalMap.put(input_row.Acc_no, input_row.Value)

on tMap, set the expression of Value column as;
row4.Flag.equals("Y")?(String)globalMap.get(row4.Acc_no):row4.Value

Hope it helps!

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: Update previous rows in tMap or tJava

Hi
First, filter the null value and then do aggregation using tAggregateRow to get the first value of each Acc_no, output the value and put it to global variable for each Acc_no, the job looks like:
tfileinputdelimited1--tfilterRow--tAggregateRow--tJavaRow
|
onsubjobok
|
tfileinutDelimited2-row4-->tMap---tfileoutputDelimited
on tJavaRow:
globalMap.put(input_row.Acc_no, input_row.Value)

on tMap, set the expression of Value column as;
row4.Flag.equals("Y")?(String)globalMap.get(row4.Acc_no):row4.Value

Hope it helps!

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Nine Stars

Re: Update previous rows in tMap or tJava

I am little late to the party as @shong has already provided a solution. Anyways here goes mine -

Job Layout - 

tSortRow-1.JPG

So, basically using tSortRow and tFilterRow tried to find and filter out the non-empty records from the file and as a result tHashOutput would hold the distinct non null values from the input file.

On second step, read the file again and use tMap to join the original file content with the tHashOutput content to get the required output.

tMap configuration - 

tMap-TC2.JPG

Seven Stars

Re: Update previous rows in tMap or tJava

Appreciate effort both of you @iamabhishek @shong but Sachin already mentioned in his question that they already having lookup with him. So, job would be like:

 

one.PNGtwo.PNG

Regards
Aashish
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Community Manager

Re: Update previous rows in tMap or tJava

ah..yes, if the lookup is already available, it is more simple to achieve it like you did. @aashish

----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Update previous rows in tMap or tJava

Thanks and Kudos to @shong , @iamabhishek , @aashish for taking time and providing solution.