Check individual lines against the interval

Wed, 07 Mar 2012 16:09:52 GMT

What is the fastest way to check a set of individual data lines against a from to range ?

Batch:

LOAD

batch_number,

invoice_number_from,

invoice_number_to

resident batch_header;

Invoices:

LOAD

batch_number,

invoice_number,

...

resident invoice_header;

Table Batch contains a range of invoices asked with a from and to invoice number. 1 row per batch_number

Table Invoices containts my actual invoices. 1 row per batch_number / invoice_number

However, for some reason, table Invoices sometimes does not contain some invoices as they cannot be processed by the interface process.

How can I easily script this ? I have the invoice_number_to and invoice_number_from in table Batch and I want to check which invoice numbers in between the range cannot be found in the table Invoices. Below is an example

Table Batch :

batch_number invoice_number_from invoice_number_to

123456 12568 12574

Table Invoices:

batch_number invoice_number

123456 12568

123456 12569

123456 12571

123456 12572

123456 12573

The result I want to get is a table with 12570 and 12574 as these numbers are within the interval but cannot be found in table Invoices

I guess the easiest way might be to transform the Table Batch into a multi row table and then check the generated invoice numbers against the Invoices table, but what is the easiest way to do this ?

topic Check individual lines against the interval in QlikView

Check individual lines against the interval