One Star

how to replace null values with earlier row's values

hi,
I am having data like
accountno acctname acctdefn
--------------------------------------
AS123456 savings saving acct
deposit deposit acct
transfer transfer acct
BS789101 savings saving acct
transfer transfer acct
in this data, the 2nd and 3rd rows won't contain data for account no. so, It has to replace with the previous row not null value( 1st row data of accountno i.e, AS123456).
same as, 5th row won't contain data for accountno, so it has to replace with the previous row not null value (4th row account no).
i want data like
accountno acctname acctdefn
--------------------------------------
AS123456 savings saving acct
AS123456 deposit deposit acct
AS123456 transfer transfer acct
BS789101 savings saving acct
BS789101 transfer transfer acct
please reply.
9 REPLIES

Re: how to replace null values with earlier row's values

you could easily do this with the globalMap and a tJavaRow component.
The idea would be for every column to check if the row value is null, and then either save it or replace with the previous not null value. for a schema of one column (COLUMN_NAME) it would look like this:
if( input_row.COLUMN_NAME != null ) {
//input row is NOT NULL, cache this row value in globalMap and assign to output row
globalMap.put("COLUMN_NAME", input_row.COLUMN_NAME);
output_row.COLUMN_NAME = input_row.COLUMN_NAME;
}
else {
//input column IS NULL. retrieve previous row value from globalMap
//if this is the first row, the globalMap will not be populated, so check if the key exists and if not, assign default value
if( (String)globalMap.get("COLUMN_NAME") != null ) {
output_row.COLUMN_NAME = (String)globalMap.get("COLUMN_NAME");
}
else {
output_row.COLUMN_NAME = "DEFAULT VALUE";
}
}

edit: There is also a component "tMemorizeRows" that may be helpful. I am not familiar with it, so Im not sure it will help-- but it seems like it might be worth a look.
One Star

Re: how to replace null values with earlier row's values

you may have to store your ac nos locally .

Should this work ? @JohnGarrettMartin
if(input_row.accountno != null)
{
//globalMap.put("col1", input_row.accountno);
globalMap.put("col1", input_row.accountno);
output_row.accountno = input_row.accountno;
}
else
{
if((String)globalMap.get("col1") != null)
output_row.accountno = (String)globalMap.get("col1");
else
output_row.accountno = "NO VALUE";
}
output_row.acctname = input_row.acctname;
output_row.acctdefn = input_row.acctdefn;

Re: how to replace null values with earlier row's values

should work fine except that I would reject this code due to the lack of brackets around your inner if/else. Smiley Wink
One Star

Re: how to replace null values with earlier row's values

ps!!
I thought so 2 ..that the code should work.
But i dont see any change in the o/p it is being displayed just as the input.As if the code did not execute!
Do i have to check anything to get the code considered.
I just have an inputdelimitedfile-->tJavaRow-->tLogRow
The tLogRow has the same o/p as i/p

Re: how to replace null values with earlier row's values

do you have any null values in your "accountno" column? if there are none, then the code would appear to not do anything-- which is how it should work.
One Star

Re: how to replace null values with earlier row's values

hi,
thanks and it works.
i am having another issue,
if I have data like
;saving;saving acct
;transfers;transfer acct
AS123456;deposits;deposits acct
;peti;peti acct
PT7891011;saving;saving acct
and I want data likes
AS123456;saving;saving acct
AS123456;transfers;transfer acct
AS123456;deposits;deposits acct
PT7891011;peti;peti acct
PT7891011;saving;saving acct
how to do it?
One Star

Re: how to replace null values with earlier row's values

Hi John
but your above code would only work for DB Tables which have nulls for files you may have to check if they are empty or not.

Re: how to replace null values with earlier row's values

If you want to count the empty string ("") as null, there is an option in (most of) the input components to convert it to a java null. Otherwise, you can update the "if" statements to check for empty string:
if(input_row.accountno != null) {

would become:
if(input_row.accountno != null || !"".equals(input_row.accountno) ) {
One Star

Re: how to replace null values with earlier row's values

Correct...
i instead checked for its length if it is not 0.