Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to identify missing records on a table

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?

1 Solution

Accepted Solutions
eduardo_sommer
Partner - Specialist
Partner - Specialist

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

View solution in original post

8 Replies
Not applicable
Author

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


talk is cheap, supply exceeds demand
eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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.

Not applicable
Author

Thank you!

silambarasan
Creator II
Creator II

Can you give sample record?

eduardo_sommer
Partner - Specialist
Partner - Specialist

you're welcome

Not applicable
Author

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.