Key Change - Increment Value
Moderators: chulett, rschirm, roy
Key Change - Increment Value
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
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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
Then, you can use 'Stage Variables' or 'RowProcCompareWithPreviousValue' routine to achieve that.
Search on RowProcCompareWithPreviousValue to learn how to use it.
HTH,
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
I'mDSguru2B wrote: store it in a stored procedure,
![Confused :?](./images/smilies/icon_confused.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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:I'mDSguru2B wrote: store it in a stored procedure,. 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.
![Embarassed :oops:](./images/smilies/icon_redface.gif)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
Does the function RowProcCompareWithPreviousValue work in PX?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.
in my transformer stage I can't seem to find it as I can on a server job.
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
We all have days like that...
svarChanged <== IF svarLastFieldB = In.FieldB THEN 0 ELSE 1
svarLastFieldB <== In.FieldB
svarCounter <== IF svarChanged THEN 1 ELSE svarCounter + 1
svarLastFieldB <== In.FieldB
svarCounter <== IF svarChanged THEN 1 ELSE svarCounter + 1
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.