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

Question

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

Sample Data:

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

score 0 · Accepted Answer · answered Jun 12 '19 at 05:54

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.

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

1 Answers1