I hava a design question, I have a Table that look like this:
|ID||SourceID||SeqNo||Date|| ...... Other fields|
What I need to find is the diffrent missing seqNo ranges for each SourceID with its corresponding date range
|SourceID||SeqNo1||SeqNo2||Missing||Date 1||Date 2|
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.