topic Update previous rows in tMap or tJava in Talend Studio

Update previous rows in tMap or tJava

SachinD — Wed, 25 Jul 2018 18:18:08 GMT

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.

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

Re: Update previous rows in tMap or tJava

Anonymous — Thu, 26 Jul 2018 08:11:13 GMT

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

Re: Update previous rows in tMap or tJava

iamabhishek — Thu, 26 Jul 2018 08:41:27 GMT

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

Job Layout -

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 -

Re: Update previous rows in tMap or tJava

aashish_21nov — Thu, 26 Jul 2018 09:40:22 GMT

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

Re: Update previous rows in tMap or tJava

Anonymous — Thu, 26 Jul 2018 09:45:23 GMT

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

Re: Update previous rows in tMap or tJava

SachinD — Thu, 26 Jul 2018 14:58:41 GMT

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