List Missing sequence

Mon, 05 Aug 2013 21:35:56 GMT

Helo,

I hava a design question, I have a Table that look like this:

Table:

ID	SourceID	SeqNo	Date	...... Other fields
1	Source1	1	01/01/2013	.......
2	Source2	1	01/01/2013	.......
3	Source3	1	01/01/2013	.......
4	Source1	2	02/01/2013	.......
5	Source1	5	03/01/2013	.......
6	Source2	2	02/01/2013	.......
7	Source3	3	03/01/2013	.......
8	Source1	6	04/01/2013	.......
9	Source2	3	04/01/2013	.......
10	Source1	8	05/01/2013	.......

What I need to find is the diffrent missing seqNo ranges for each SourceID with its corresponding date range

Somthing like:

SourceID	SeqNo1	SeqNo2	Missing	Date 1	Date 2
Source1	2	5	2	02/01/2013	03/01/2013
Source3	1	3	1	01/01/2013	03/01/2013
Source1	6	8	1	04/01/2013	05/01/2013

So far i've done:

//// I wrote this code directly into the blog's editor, not the QlikView script editor, so it may contain syntax errors
/// Load table
Tabla:
Load id,
        SourceID,      
        SeqNo
        SeqNo-1 as SeqNo_ant,
        Date
From Table.qvd
///Join data of record for seqno -1 for each sourceID.
Left Join (Table)
Load SourceID,                        
        SeqNo as SeqNo_ant,  
        Id as Id_Ant
From Table.qvd;
/// Get only records with no previos seqno
Table2:
NoConcatenate
Load id, 
        SourceID, 
        SeqNo, 
        Date
Resident Table
where Id_ant is null 
and id > 1;    /// omit case of sequence 1 
/// loop each record in Table2 and look for previos sequence for the SourceId in Table and join the rest of record 
let NRows = NoOfRows('Table2');
For I = 0 to $(NRows) 
   Let xSource = Peek('SourceID', $(I), 'Table2'); 
   Let xSeqNo = Peek('SeqNo', $(I), 'Table2');
   /// Get the maximum SeqNo less than xSeqNo and join to Table2 for each  SourceId, SeqNo
   left Join (Table2)
   Load SourceID,
           $(xSeqNo) as SeqNo,
           Max(SeqNo) as Prev_SeqNo
   Resident Table
   where SourceID = $(xSource)
      and SeqNo < $(xSeqNo)
   group by SourceID;
next I
//// get the date field for previous seqno
Left Join (Table2)
Load SourceID, 
        SeqNo as Prev_SeqNo,
        Date as Prev_Date
resident Table;
Drop table Table;

That is pretty close to what I need, but it takes forever to run.

Any ideas on how to improve this?. In particular, how to get rid of the loop, which is the most time consuming part.

Thanks in advance.

topic List Missing sequence in QlikView

List Missing sequence