0 Replies Latest reply: Aug 8, 2013 2:37 PM by Walter Charrière RSS

    List Missing sequence

    Walter Charrière

      Helo,

       

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

       

      Table:

      IDSourceIDSeqNoDate ...... Other fields
      1Source1101/01/2013.......
      2Source2101/01/2013.......
      3Source3101/01/2013.......
      4Source1202/01/2013.......
      5Source1503/01/2013.......
      6

      Source2

      202/01/2013.......
      7Source3303/01/2013.......
      8Source1604/01/2013.......
      9Source2304/01/2013.......
      10Source1805/01/2013.......

       

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

      Somthing like:

       

       

      SourceIDSeqNo1SeqNo2
      MissingDate 1Date 2
      Source125202/01/201303/01/2013
      Source313101/01/201303/01/2013
      Source168104/01/201305/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.