Identify missing sequential number

[jeffrey_a_lin]

Hi Everyone,

I wanted to inquire if there's an expression code to calculate a sequential missing number in Qliksense

Eg.
Invoice Numbers

1. 1000020
2. 1000021
3. 1000022
4. 1000023
5. 1000024
6. 1000026
7. 1000027
8. 1000029
9. 1000031

     ..........

Expected Output: 1000025, 1000028, 1000030

In excel I can perform this using  =IF(ISNA(VLOOKUP(ROW(A1),$A1:$A$13,1,FALSE)),ROW(A1),"")

Is there an expression code I can use in Qliksense table to indicate the missing numbers ?

[christophebrault]

Hi,

try this script :

Invoices:

load * inline [

InvoiceNum

1000020

1000021

1000022

1000023

1000024

1000026

1000027

1000029

1000031

];

// invoiceNum have to be ordered asc for this script to work fine

let vMin= peek('InvoiceNum',0,'Invoices'); // find the first InvoiceNum

let vMax= peek('InvoiceNum',NoOfRows('Invoices')-1,'Invoices'); // find the last

MissingNumbers:

LOAD *

where not exists(InvoiceNum,Numbers)

;

LOAD '$(vMin)'+iterno() as Numbers

autogenerate 1

while '$(vMin)'+iterno() <= '$(vMax)';

[jeffrey_a_lin]

Hi Christopher,

Thanks for the suggestion

I was wondering if I could do the same in an expression code as I don't have access to the source data to reload the load script.

Thanks in advance,

Jeff

[christophebrault · Accepted solution]

Hi,

Find the app attached

Quick description :

First create 3 variables :

vMin : =Min(InvoiceNum)

vMax : =Max(InvoiceNum)

List : =concat(InvoiceNum,',')

Create a Table with a dimension :

=ValueLoop(vMin,vMax,1)

And a mesure

if(match( ValueLoop(vMin,vMax,1), $(List) )>0,0,1)

Uncheck "inclure les valeurs Zéros"

And it's done !

[jeffrey_a_lin]

Thank you so much Christophe

Exactly what I was looking for, thank you so much. I was initially thinking of using a Rank function

NUM(Rank(Total AVG (-([InvoiceNum]))))

but couldn't think of how I could use that. Your solution was much better.

Thanks once again

[christophebrault]

My pleasure , there isn't so much time when we can use valueloop  

[respondek]

Hi Christophe,

have following invoice numbers but they are not num.

How can I identify missing numbers?

12-2016/0001

12-2016/0002

12-2016/0003

12-2016/0004

01-2017/0001

01-2017/0002

01-2017/0003

01-2017/0006

01-2017/0007

01-2017/0008

01-2017/0009

01-2017/0010

01-2017/EB01

02-2017/0001

02-2017/0002

02-2017/0004

03-2017/0001

03-2017/0002

03-2017/0003

03-2017/0004

03-2017/0005