5 Replies Latest reply: Jun 15, 2018 2:59 AM by Christophe Brault

# Identify missing sequential number

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 ?

• ###### Re: Identify missing sequential number

Hi,

try this script :

Invoices:

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:

where not exists(InvoiceNum,Numbers)

;

autogenerate 1

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

• ###### Re: Identify missing sequential number

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.

Jeff

• ###### Re: Identify missing sequential number

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 !

• ###### Re: Identify missing sequential number

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

• ###### Re: Identify missing sequential number

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