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

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

matthewp — Tue, 03 May 2016 11:08:01 GMT

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

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

Mahamed_Qlik — Tue, 03 May 2016 11:16:39 GMT

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

A1001

A1002

A1003

A1004

A1005

A1006

A1007

A1008

A1009

A1010

A1011

A1012

A1014

A1015

A1016

A1017

A1018

A1020

Regards,
Mahamed

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

matthewp — Tue, 03 May 2016 11:22:50 GMT

neither. its just simply not there

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

MayilVahanan — Tue, 03 May 2016 11:29:27 GMT

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
A1001	1001
A1002	1002
A1003	1003
A1004	1004
A1005	1005
A1006	1006
A1007	1007
A1008	1008
A1009	1009
A1010	1010
A1011	1011
A1012	1012
	1013	1
A1014	1014
A1015	1015
A1016	1016
A1017	1017
A1018	1018
	1019	1
A1020	1020

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

Mahamed_Qlik — Tue, 03 May 2016 11:33:35 GMT

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

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

Kushal_Chawda — Tue, 03 May 2016 11:46:40 GMT

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

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

matthewp — Tue, 03 May 2016 12:11:20 GMT

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