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.
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.
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
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.
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?
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:
