while loop data order with Sequence id

[#1]

Hi ,

we are trying to add sequence to below raw table using Master table. Sequence number is required

Table: ( Below table having details of each Leg  Anode [i.e. Start] & Znode [i.e End] details )

ID,LegID,Anode,Znode

S1,MSB2,N2,N3

S1,MSB1,N1,N2

S1,MSB4,N4,N5

S1,MSB3,N3,N4

S2,MSB2,N2,N3

S2,MSB5,N3,N6

MasterTable: ( This Master table having details of start & End details )

ID, StartNode, EndNode

S1,N1,N5

S2,N2,N6

we required data with sequence ID in below format

Output:

ID,Cktid, SequenceID

S1,MSB1,1

S1,MSB2,2

S1,MSB3,3

S1,MSB4,4

S2,MSB2,1

S2,MSB5,2

Can any one help,  Thank's in advance.

regards

Rajesh

[anbu1984]

You can get sequence number using sql

select ID,LegID, row_number() over(partition by ID order by LegID) from Table

[maxgro]

source:

load * inline [

ID,LegID,Anode,Znode

S1,MSB2,N2,N3

S1,MSB1,N1,N2

S1,MSB4,N4,N5

S1,MSB3,N3,N4

S2,MSB2,N2,N3

S2,MSB5,N3,N6

];

table:

load

AutoNumber(ID & '-' & LegID, ID) as Sequence,

*

Resident source

order by ID, LegID;

DROP Table source;

[#4]

Thanks for update...

LegId is not in sequence always .

  Our logic is need to get the sequence number  using ANode -> Z Node traversing.

        From Master table select the ID,Start Node  & Logic  in source table is

            1. Select  row using ID ( Value#S1) & Anode ( Value#N1)

                                For above selection need to return Sequence as "1"

                                 i.e.  ID LegID Sequence

                                        S1 MSB1 1

            2. For Second sequence Legid , Business Logic:  Znode of previous sequence will be Anode in current sequence. In Below Case MSB3  will come as Second Sequence.

                                         S1 MSB3 2

                                         S1 MSB5 3

                                         S1 MSB2 4

MasterTable: ( This Master table having details of start & End details )

ID, StartNode, EndNode

S1,N1,N5

S2,N2,N6

Below is the example ,

source:                                      

ID,LegID,Anode,Znode

S1,MSB3,N2,N3

S1,MSB1,N1,N2

S1,MSB2,N4,N5

S1,MSB5,N3,N4

S2,MSB101,N2,N3

S2,MSB6,N3,N6

And final out put will be

ID,LegID,Anode,Znode , Sequence

S1,MSB1,N1,N2,1

S1,MSB3,N2,N3,2

S1,MSB5,N3,N4,3

S1,MSB2,N4,N5,4

S2,MSB101,N2,N3,1

S2,MSB6,N3,N6,2

regards

Rajesh