Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can get sequence number using sql
select ID,LegID, row_number() over(partition by ID order by LegID) from Table
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;
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