Row by row execution

[dsisbank]

Hi,
I have a sequential file that has duplicate values on three key fields.I need to update it to a db2 stage.I have to keep non null values of the previous record of the sequential file if the other record has null values in the same fields.
I tried to use oracle stage to use sparse lookup but I still loose the previous nonnull values.
Do u know any ways to do this?
Thanks...

[kumar_s]

You can use Transformer Stage variable to check if previous value of the rows were not Null and the current row values were Null, then you can replace the current row value to previous row value, if the key fields are same.
You can have additional field to flag the row. You need to filter out the rows based on the flag.

[dsisbank]

How I ll get the previous values?

[dsisbank]

The sequential file has data like this

123;456;789;1;;;1;
123;456;789;;2;;2;
123;456;789;;;3;3;

I need to have this data in the target

123;456;789;1;2;3;3;

[pigpen]

I think of an aggregator and get the max() value of the last 4 columns.

[dsisbank]

But max value is not the solution for my case.

Data can be like this

123;456;789;1;;;3;
123;456;789;;2;;2;
123;456;789;;;3;1;

I need to have this data in the target

123;456;789;1;2;3;1;

I need to update nonnull fields with the last record

[kumar_s]

Code: Select all

vKey1 = Keycolumn1
vKey2 = Keycolumn2
vKey3 = Keycolumn3

vKeyCheck => If vKey1 = vPrevKey1 and vKey2 = vPrevKey2 and vKey3 = vPrevKey3 Then 1 Else 0
vPrevKey1 = vKey1
vPrevKey2 = vKey2
vPrevKey3 = vKey3


vCol1 => If vKeyCheck = 1 And IsNull(vCol1) Then vPrevCol1 Else vCol1
vPrevCol1 = vCol1

...
...
...
Similarly for other columns

You ll to set a flag for row you need to fetch.

[pigpen]

You ll to set a flag for row you need to fetch.

... or make use of a hash file.

[kumar_s]

Hash file will retain either first or last row within the group not the max or mix for each field.

[dsisbank]

I didn't understand how I get the previous values in transformer...

[pigpen]

I guess RowProcGetPreviousValue().
Well, Kumar, if I use Keycolumn1, Keycolumn2 and Keycolumn3 as hash key and one output link to the hash file, can I assume the record in the hash file is the last record written by your code?

[DSguru2B]

I didn't understand how I get the previous values in transformer...

Its the order of execution. The stage variables are executed in the order they are defined so the last variable will have the previous rows value by the time the first stage variable is being executed for the current row.

[vipshiva]

I got correct o/p using stage variables...

So try to implement using Kumar's logic.....

[roy]

I guess in a parallel job or in a server job you can use an aggregator stage setting 3 key columns and using max() on all other fields to get the job done.

And please let me know if it's a parrallel job so I can move it to the proper forum

IHTH (I Hope This Helps),