Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jeffrey_a_lin
New Contributor

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
christophebraul
Contributor III

Re: Identify missing sequential number

Hi,

Find the app attached

Quick description :

img304701.PNG

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"

img304701bis.PNG

And it's done !

6 Replies
christophebraul
Contributor III

Re: Identify missing sequential number

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
New Contributor

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.

Thanks in advance,

Jeff

christophebraul
Contributor III

Re: Identify missing sequential number

Hi,

Find the app attached

Quick description :

img304701.PNG

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"

img304701bis.PNG

And it's done !

jeffrey_a_lin
New Contributor

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

christophebraul
Contributor III

Re: Identify missing sequential number

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

respondek
New Contributor

Re: Identify missing sequential number

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