DSXchange

I am trying to code the following logic in DataStage Transformer;

Field A Field B Value
1 A 1
1 A 2
1 A 3
1 B 1
1 B 2
2 C 1
2 C 2

As you see once the key value changes, I recommence the value. I tried writing an if then else but I believe that there must be a more efficient way. Isn't there a system value that will flag me when a change happens. If not, how can I set the previous value equal to the record just read.

Thanks

There is. Assuming that the fields, Field A and Field B are incoming, and based on those, you are trying to set the value.

Then, you can use 'Stage Variables' or 'RowProcCompareWithPreviousValue' routine to achieve that.

Search on RowProcCompareWithPreviousValue to learn how to use it.

HTH,
Whale.

Concatenate the first two columns, store it in a stored procedure, check with incoming, if its same, increment the counter else reset to 1. Search this forum, this has been covered gazillions of times.

DSguru2B wrote: store it in a stored procedure,

I'm . The OP did not specify that the data is being extracted from a db table. I guess you assumed. Hope it's right. Or did I miss something?

Whale.

Thanks, I am extracting from a DB2 table. I searched more posts and used a previous solution. Stage variable did the trick.

I_Server_Whale wrote:

DSguru2B wrote: store it in a stored procedure,

I'm . The OP did not specify that the data is being extracted from a db table. I guess you assumed. Hope it's right. Or did I miss something?

Whale.

What a bummer. I meant stage variable. I was thinking of stored procedure while i was typing out my reply and hence my blunder.

I_Server_Whale wrote:There is. Assuming that the fields, Field A and Field B are incoming, and based on those, you are trying to set the value.

Then, you can use 'Stage Variables' or 'RowProcCompareWithPreviousValue' routine to achieve that.

Search on RowProcCompareWithPreviousValue to learn how to use it.

HTH,
Whale.

Does the function RowProcCompareWithPreviousValue work in PX?
in my transformer stage I can't seem to find it as I can on a server job.

No, RowProcCompareWithPreviousValue is a server-based SDK routine and will not work in PX.

ArndW wrote:No, RowProcCompareWithPreviousValue is a server-based SDK routine and will not work in PX. ...

Thank you; is there any way to compare previous values in PX? Any alternative function? I need to achieve the same result. I'm hoping there's a way.

Regards

As noted above, just use stage variables.

Folks,

I'm confused as to how I can use stage variables for this problem. Taking the example cited above:
Field A Field B Value
1 A 1
1 A 2
1 A 3
1 B 1
1 B 2
2 C 1
2 C 2

If I define a stage variable (type Varchar 255) to store the concatenated result of Filed A and Field B and another one (type Binary) to detect if in the incoming row the same concatenation is different from the value stored earlier I don't think this would be possible. This would be because for each incoming row the stage variable would be redefined with the concatenated value of Field A and Field B.

Fyi. I have as source a dataset and thus not table.

svarChanged = IF svarLastFieldB = In.FieldB THEN 0 ELSE 1
svarLastFieldB = In.FieldB
svarCounter = IF svarChanged THEN svarCounter = 1 ELSE svarCounter = svarCounter + 1



Derivation of Out.Value = svarCounter

svarChanged <== IF svarLastFieldB = In.FieldB THEN 0 ELSE 1
svarLastFieldB <== In.FieldB
svarCounter <== IF svarChanged THEN 1 ELSE svarCounter + 1

Thanks for that.