Update previous rows in tMap or tJava

[SachinD]

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

[Anonymous · Accepted solution]

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

[iamabhishek]

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 - 

[aashish_21nov]

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

[Anonymous]

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

[SachinD]

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