topic Re: Update Row Values Dynamically in Talend Studio

Update Row Values Dynamically

SSingh1687332860 — Fri, 15 Nov 2024 21:37:41 GMT

Hi, i need to update row values dynamically like in the example.

Job: input -row1-> tMemorizeRows -row2-> tJavaFlex -row3-> tLogRow

Example:

if(condition)

update/change values from current row

else

update/change values from previous row

How can i do it? Is it possible?

Re: Update Row Values Dynamically

anselmopeixoto — Mon, 03 Jul 2023 12:03:51 GMT

Hi @Sunny Singh

Let's suppose you have an input like this:

key|col1|col2|col3

1|A|B|C

2|D|E|F

3|G|H|I

And your condition is "if current key value is even, update previous row columns using current row values".

Then you can use a code like the following on tJavaRow (or tJavaFlex main code):

if (input_row.key % 2 == 0) {

//if current key is even, output its columns but using previous row key

output_row.key = (key_tMemorizeRows_1[1] != null? key_tMemorizeRows_1[1] : input_row.key);

output_row.colA = input_row.colA;

output_row.colB = input_row.colB;

output_row.colC = input_row.colC;

} else {

//if current key is odd, output current row as is

output_row.key = input_row.key;

output_row.colA = input_row.colA;

output_row.colB = input_row.colB;

output_row.colC = input_row.colC;

}

You'll notice through tLogRow that the code above doesn't actually update previous row. That's not possible since it was already processed.

You'll need a tHashOutput after tJavaRow/tJavaFlex and configure it to "Keep last" on keys management. You'll also need to set the key on its schema.

Then you can read what's stored in memory using a tHashInput linked to that tHashOutput and the output will be:

1|D|E|F <--- column values from key 2 replaced its previous row with key value 1

3|G|H|I

Re: Update Row Values Dynamically

SSingh1687332860 — Mon, 03 Jul 2023 12:49:32 GMT

Hi, is it possible to keep all the rows and change values like in the example?

Begin:

key|col1|col2|col3

1|A|B|C

2|D|E|F

3|G|H|I

End:

key|col1|col2|col3

1|A|B|F <-- value changed in the previous row

2|G|E|F <-- value changed in the previous row

3|G|H|I

I need to change the value without replacing it.

Re: Update Row Values Dynamically

anselmopeixoto — Mon, 03 Jul 2023 13:08:42 GMT

Is the number of rows you need to keep in memory dynamic?

Re: Update Row Values Dynamically

SSingh1687332860 — Mon, 03 Jul 2023 14:01:13 GMT

The number of rows is fixed. In the example there are 5 rows.

Here a brief example of what i need to do.

Example

Begin:

key|col1|col2|col3|col4

1|A|B|C|null //row1

2|D|E|F|null //row2

3|G|H|F|null //row3

4|T|V|S|null //row4

5|X|W|S|null //row5

End:

key|col1|col2|col3|col4

1|A|B|C|false //row1

2|D|E|F|true //row2

3|G|H|F|false //row3

4|T|M|S|true //row4

5|X|W|S|false //row5

I want to write something like this:

if(row2.col3 == col3_tMemorizeRows_1[1] && col3_tMemorizeRows_1[1] != null) {

set col4 = true in the previous row

else

set col4 = false in the current row

else ...

Basically the col4 is true in the previous row when the current value of col3 is equal to the previous value of col3. Otherwise col4 is false.

Re: Update Row Values Dynamically

anselmopeixoto — Mon, 03 Jul 2023 14:50:06 GMT

Ok, from what I've got I believe what you need to do is join this dataset with itself using tMap component and col3 as join criteria.

And then on col4 you can use an expression like this:

row13.key != null && row12.key == row13.key? true : false

This way it won't be even necessary to memorize the previous rows.

I've attached some screenshots with an example. Here's what it will produce:

1|A|B|C|true

2|D|E|F|true

3|G|H|F|false

4|T|V|S|true

5|X|W|S|false