I have the following table.
Rno Pno Dno Cno
123 222 0 0
321 222 1 2
213 222 2 1
432 111 0 1
342 111 1 0
562 333 0 0
721 444 0 0
271 444 1 1
Ouput
Rno Pno Dno Cno rtno
123 222 0 0 123
321 222 1 2 123
213 222 2 1 123
432 111 0 1 null
342 111 1 0 null
562 333 0 0 562
721 444 0 0 721
271 444 1 1 721
For every Dno=0 and Cno=0 , the rtno for every Pno will have the Rno in it.
Where Cno or Dno are not 0(any one) those all will be null for each Pno.
Could someone tell me the solution to this please.
Group On One Column and Get Data from Other Column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 19
- Joined: Tue Jul 16, 2013 10:21 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard. Try this:
Code: Select all
If Cno = 0 And DNo = 0 Then RNo Else SetNull()
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.
-
- Premium Member
- Posts: 19
- Joined: Tue Jul 16, 2013 10:21 am
That will just give me the Rtno where Cno=0 and Dno=0. My requirement is that, I need the First Rno, for Every same Pno(Dupes) where Cno=0 and Dno=0.
Like in the below example if dno=0 and cno=0 then for every pno=222 I need rtno=123.
Rno Pno Dno Cno rtno
123 222 0 0 123
321 222 1 2 123
213 222 2 1 123
If for instance I don't have a group of same pno where cno or dno is not zero, then I need it as null.
So inshort. if pno with 444 has one record with cno=0 and dno=0 then the rtno=721 for all pno=444 . If in case it did not have a record where cno=0 and dno=0 , then it would have rtno=null for all pno's.
Like in the below example if dno=0 and cno=0 then for every pno=222 I need rtno=123.
Rno Pno Dno Cno rtno
123 222 0 0 123
321 222 1 2 123
213 222 2 1 123
If for instance I don't have a group of same pno where cno or dno is not zero, then I need it as null.
So inshort. if pno with 444 has one record with cno=0 and dno=0 then the rtno=721 for all pno=444 . If in case it did not have a record where cno=0 and dno=0 , then it would have rtno=null for all pno's.
sunnym
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your original requirement was not totally clear on this. Use stage variables to remember the value from the previous row and to detect group change (Dno = 0 and Cno = 0). This of course requires sorted data.
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.
-
- Premium Member
- Posts: 19
- Joined: Tue Jul 16, 2013 10:21 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need to determine when a new group is starting. Set up a stage variable svIsNewGroup derived as Or, if that's not correct, sort the data by PNo and generate a KeyChange column.
Derive the value of rtno in a stage variable svRtNo as
Output column rtno is derived as the stage variable svRtNo
(If you're on a version that does not support null stage variables, use a value that will never appear in the data, such as -99, rather than SetNull() in the stage variable expression, and derive rtno as If svRtNo = -99 Then SetNull() Else svRtNo)
Code: Select all
DNo = 0 Or CNo = 0
Derive the value of rtno in a stage variable svRtNo as
Code: Select all
If svNewGroup Then If (DNo = 0 And CNo = 0) Then RNo Else SetNull() Else svRtNo
(If you're on a version that does not support null stage variables, use a value that will never appear in the data, such as -99, rather than SetNull() in the stage variable expression, and derive rtno as If svRtNo = -99 Then SetNull() Else svRtNo)
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.
-
- Premium Member
- Posts: 19
- Joined: Tue Jul 16, 2013 10:21 am
These responses don't help me as I was looking for a generic solution using a stage variables. Some of them are partially correct but still don't give me a complete solution to my problem. Nevertheless I had to use a lookup stage and left join on PNO with the same table where cno=0 and dno=0 and grab the rno from the reference table. This was the solution to my question. I still believe that this could be handled through stage variables. If someone has the solution. Let me know. Non of the above holds completely true. Thanks all for your responses.
sunnym