Group On One Column and Get Data from Other Column

[sunnymdatastage]

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.

[ray.wurlod]

Welcome aboard. Try this:

Code: Select all

If Cno = 0 And DNo = 0 Then RNo Else SetNull()

[sunnymdatastage]

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.

[ray.wurlod]

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.

[sunnymdatastage]

I am aware that I will have to use stage variables. But it looks like its not a simple solution. I might have to use looping as well as functions and a generic solution. Could you please alleviate the solution to me with stage variable pattern looking at the example above.

[ray.wurlod]

You need to determine when a new group is starting. Set up a stage variable svIsNewGroup derived as

Code: Select all

DNo = 0 Or CNo = 0

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

Code: Select all

If svNewGroup Then If (DNo = 0 And CNo = 0) Then RNo Else SetNull() Else svRtNo

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)

[sunnymdatastage]

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.