topic Re: how to replace null values with earlier row's values in Talend Studio

how to replace null values with earlier row's values

raj99 — Sat, 16 Nov 2024 12:28:16 GMT

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.

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

Anonymous — Fri, 06 Jan 2012 19:08:20 GMT

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.

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

Anonymous — Fri, 06 Jan 2012 19:49:35 GMT

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

Anonymous — Fri, 06 Jan 2012 20:29:20 GMT

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

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

Anonymous — Fri, 06 Jan 2012 20:39:11 GMT

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

Anonymous — Fri, 06 Jan 2012 20:50:02 GMT

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.

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

raj99 — Sat, 07 Jan 2012 09:21:22 GMT

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?

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

Anonymous — Sat, 07 Jan 2012 12:27:55 GMT

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

Anonymous — Mon, 09 Jan 2012 16:49:34 GMT

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) ) {

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

Anonymous — Mon, 09 Jan 2012 17:00:36 GMT

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