How to identify missing records on a table

[#1]

Hi

I have a table in my Qlikview model with numeric record ID's that follow on each other.

I now want to identify where there are missing ID's on the table.

What is the best way to do that?

[#2]

To add to my question above:

One way that I thought of doing this is to create a separate table starting from the minimum number on the record ID's and to autonumber up to the max record ID on the main table.

If I could do this I could then in a qlikview chart item identify the ID's that is on the one table and not on the other table.

I am sure there is an easier way to do this, but assuming this is the best way to go about, could somebody please assist me with how to create auto numbers from a set minimum to a set maximum within a new separate table.

Many thanks.

[Gysbert_Wassenaar]

You could use one of the options from this document: Generating Missing Data In QlikView

Perhaps like this, but without the last step that fills in the missing rate field values: How to populate a sparsely populated field

[eduardo_sommer · Accepted solution]

Hi, Yessica

Supose your original table has the fields (RecordID, Field1, Field2), and you want to find the gaps in the RecordID.

The following script will create a table with all the gaps:

TableAnalysis:

Load RecordID,

        if ((RecordID - peek(RecordID)) > 1, 'Records between ' & peek(RecordID) & ' and ' & RecordID & ' missing', 'Ok')                                                           as Message

Resident [Original Table]

Order by RecordID;

Hope this helps you

Eduardo

[#5]

You can use IntervalMatch to generate all the numbers between your min and max value. You can than use this table to compare any missing values.

Hope this helps.

[#6]

Thank you!

[silambarasan]

Can you give sample record?

[eduardo_sommer]

you're welcome

[#9]

I would like to take this further -

With your help I can now count the number of gaps I have with regards to missing records by merely counting the distinct number of messages.

If I also want to count the number of missing records (for example one gap has 3 records missing and the next gap has 2 missing records adding to 5 in total), is there an easy way you know of to do that?

Thanks.