topic Re: How to identify missing records on a table in QlikView

How to identify missing records on a table

Fri, 14 Oct 2016 07:37:36 GMT

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?

Re: How to identify missing records on a table

Mon, 17 Oct 2016 08:09:48 GMT

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.

Re: How to identify missing records on a table

Gysbert_Wassenaar — Tue, 18 Oct 2016 06:05:58 GMT

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

Re: How to identify missing records on a table

eduardo_sommer — Tue, 18 Oct 2016 12:56:27 GMT

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

Re: How to identify missing records on a table

Tue, 18 Oct 2016 13:35:38 GMT

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.

Re: How to identify missing records on a table

Wed, 19 Oct 2016 13:33:45 GMT

Thank you!

Re: How to identify missing records on a table

silambarasan — Wed, 19 Oct 2016 14:03:31 GMT

Can you give sample record?

Re: How to identify missing records on a table

eduardo_sommer — Wed, 19 Oct 2016 17:03:59 GMT

you're welcome

Re: How to identify missing records on a table

Thu, 20 Oct 2016 11:49:57 GMT

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.