Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor II

## 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 ?

1 Solution

Accepted Solutions
Specialist

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 !

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
6 Replies
Specialist

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)';

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Partner - Contributor II
Author

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

Specialist

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 !

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Partner - Contributor II
Author

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

Specialist

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Contributor II

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