Missing Records

QlikBeginner1 — Sat, 16 Nov 2024 17:49:19 GMT

Hello,

I have an app which is a report comparing two different source systems and the GWP for each source system based off a number of dimensions.

One of the source systems assigns Policy Numbers in sequence. Any gaps in range may indicate something has been missed. How can I produce a table which essentially the missing numbers to indicate if there is a missing number? For example

PC00002, PC00003, PC00004, PC00006. There would be a table which has a header of 'Missing Policies' and to show PC00005. From PC00001 to the Max of the PolicyNumbers where the Source System = 'Acturis'.

I hope I am making sense, any help would be greatly appreciated.

Kind regards,

Re: Missing Records

QFabian — Mon, 16 Nov 2020 19:25:56 GMT

Hi @QlikBeginner1 , check this please :

Data:
Load * inline [
Record
PC00002
PC00003
PC00004
PC00006
];

Record:
Load
*,
1 as Exists
Resident Data;

//Let vMin = PurgeChar(peek('Record',0,'Record'),'PC');
Let vMax = PurgeChar(peek('Record',NoOfRows('Record')-1,'Record'),'PC');

Load
'PC' & num(recno(),'00000') as Record,
0 as Exists
autogenerate ($(vMax))
Where not exists(Record,'PC' & num(recno(),'00000') );

topic Missing Records in App Development

Missing Records

Re: Missing Records