Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

Check if rows are continuous 1, 2, 3, 4, 5 etc

So i have a Table box that contains a single field "Numb":

Sample Data:

Numb 

A1001
A1002
A1003
A1004
A1005
A1006
A1007
A1008
A1009
A1010
A1011
A1012
A1014
A1015
A1016
A1017
A1018
A1020

I want to have a second table box (or straight table whatever works) with an expression that shows the row before and the row after any missing rows that don't run on in green and then the missing row in red.

Expected Output:

CheckNumb

A1012

A1013

A1014

A1018

A1019

A1020

1 Solution

Accepted Solutions
matthewp
Creator III
Creator III
Author

Solved this by adding this to the load script:

purgechar(Numb,'A') AS NumbOnly

Then adding a straight table with a dimension of:

NumbOnly


And the expression using the `ABOVE` function:


=IF(NumbOnly-Above(NumbOnly) = 1 ,'Match','Check')




View solution in original post

6 Replies
Mahamed_Qlik
Specialist
Specialist

Hi Matthewp,

I just want to confirm, the number suppose is missing in your column that is Numb then is it defined as

'Null' or '-' or empty cell into your data source (whatever it would be either excel or sql table )  ?

Like is your column values like as below :

A1001
A1002
A1003
A1004
A1005
A1006
A1007
A1008
A1009
A1010
A1011

A1012

NULL

A1014
A1015
A1016
A1017

A1018

NULL

A1020

OR

A1001
A1002
A1003
A1004
A1005
A1006
A1007
A1008
A1009
A1010
A1011

A1012

-

A1014
A1015
A1016
A1017

A1018

-

A1020

Regards,
Mahamed

matthewp
Creator III
Creator III
Author

neither. its just simply not there

MayilVahanan

Hi

Try like this

Temp:

LOAD *, KeepChar(Test, 0123456789) As Test1 Inline

[

Test

A1001

A1002

A1003

A1004

A1005

A1006

A1007

A1008

A1009

A1010

A1011

A1012

A1014

A1015

A1016

A1017

A1018

A1020

];

MinMaxTemp:

Load Min(Test1)-1 As Min, Max(Test1) as Max Resident Temp;

Let vMin = Peek('Min');

Let vMax = Peek('Max');

Join(Temp)

LOAD $(vMin)+IterNo() as Test1 AutoGenerate 1 While $(vMin)+IterNo() <= $(vMax);

Final:

LOAD Test, Test1, if(Isnull(Test), 1 ) AS Flag Resident Temp;

DROP Table Temp;

Output:

Test Test1 Flag
A10011001
A10021002
A10031003
A10041004
A10051005
A10061006
A10071007
A10081008
A10091009
A10101010
A10111011
A10121012
10131
A10141014
A10151015
A10161016
A10171017
A10181018
10191
A10201020
Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Mahamed_Qlik
Specialist
Specialist

Hi Mattew,

If it is the case then, we cant not add the data into our data model by our own.

But, if you still want to show the data which is not available into your source table then you can use

the master calendar logic which is given by Mayil above.

Regards,

Mahamed

Kushal_Chawda

try this

Data:

LOAD purgechar(Numb,'A') as NUMB,

     Numb

FROM table;

New:

LOAD Numb,

     if(NUMB<>previous(NUMB)+1,previous(NUMB),0) as NewNumbMin,

     if(NUMB<>previous(NUMB)+1,NUMB,0) as NewNumbMax

resident Data

order by NUMB ;

Final:

LOAD 'A' & CheckNumb as CheckNumb;

LOAD *,

     NewNumbMin +iterno()-1 as CheckNumb

WHILE NewNumbMin +iterno()-1 <= NewNumbMax;

LOAD *

Resident New

WHERE NewNumbMin>0 and NewNumbMax>0;

drop table New;


Take Table box of Numb and Checknumb

matthewp
Creator III
Creator III
Author

Solved this by adding this to the load script:

purgechar(Numb,'A') AS NumbOnly

Then adding a straight table with a dimension of:

NumbOnly


And the expression using the `ABOVE` function:


=IF(NumbOnly-Above(NumbOnly) = 1 ,'Match','Check')