Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

List Missing sequence

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.