Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I wanted to inquire if there's an expression code to calculate a sequential missing number in Qliksense
Eg.
Invoice Numbers
..........
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 ?
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 !
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)';
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
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 !
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
My pleasure , there isn't so much time when we can use valueloop
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 |