Search code examples
qlikviewqliksenseqlik-expression

QlikView Check if rows are continuous (1, 2, 3, 4, 5 etc) in a table

I have a Table box that contains a single field "Numb":

Sample Data:

Numb 
A1001
A1002
A1003
A1004
A1005
A1006
A1007
A1008
A1009
A1010
A1011
A1012
A1014
A1015
A1016
A1017
A1018
A1020

I want to have a second table box (or straight table whatever works) with an expression that shows the row before and the row after any missing rows that don't run on in green and then the missing row in red.

Expected Output:

CheckNumb   
A1012   
A1013   
A1014   
A1018  
A1019  
A1020
Solution

  • If you only have numbers in your data you can skip this part.

    In the load script add the following to remove the letter:

    purgechar(Numb,'A') AS NumbOnly

    Then adding a straight table with a dimension of:

    NumbOnly

    Create an expression using the ABOVE function:

    =IF(NumbOnly-Above(NumbOnly) = 1 ,'Match','Check')

    This will check if the row above is more than 1 apart.