How to achive connect by level in Qlikview?

[#1]

Hi,

I want to achive the missing sequence id's in a table.

In SQL it can be done using Connect by level.

For example

In My table I have following Data

Seq_id         FileName        Date

2012                   A              2-jan-2013

2013                   A             10-oct-2012

2014                   A              5-Jun-2013

2016                   A             15-May-2013

2018                   A             19-Jul-2013

I want the output as:-

Missing Sequence

2015

2017

Please help me in this.

[#2]

Hi rakhi,

please take a look at the following code:

Seq:

LOAD * Inline [

ID

2011

2012

2014

2016

];

MinMAx:

LOAD Min(ID)    AS MinID, Max(ID)    AS MaxID Resident Seq;

Let vMin= Peek('MinID');

Let vMax= Peek('MaxID');

RefTab:

LOAD

    $(vMin) +  IterNo()    AS   All_IDs

AutoGenerate(1)

While $(vMin) +  IterNo() <= $(vMax)

;

Missing:

LOAD

    All_IDs AS MissingVal

Resident RefTab

Where not Exists(ID, All_IDs)

;

Regards

Roland