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.
