topic Re: while loop data order with Sequence id in QlikView

while loop data order with Sequence id

Mon, 14 Apr 2014 12:33:53 GMT

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

Re: while loop data order with Sequence id

anbu1984 — Mon, 14 Apr 2014 12:50:50 GMT

You can get sequence number using sql

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

Re: while loop data order with Sequence id

maxgro — Mon, 14 Apr 2014 20:40:38 GMT

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;

Re: while loop data order with Sequence id

Tue, 15 Apr 2014 06:36:01 GMT

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