Find the missing entries

[bhaskarsharma03]

Hi,

I have a data of plants, and invoices related to those plants. The invoice numbers are alphanumeric, always ending with a number. The invoices are meant to be created by an increment of 1 (at the numeric part of the string) for each new invoice created. However, it is noted that a lot of invoice numbers are missing. In the example below, you can see the missing invoices:

InvoiceNumber	PlantCode	Comment
NS05060100001419	NS05
NS05060100001420	NS05
NS05060100001421	NS05
NS05060100001423	NS05	Invoice number ending with 22 is missing
NS05060100001424	NS05
NS05060100001428	NS05	Invoice numbers ending with 25, 26, 27 are missing
NS05060100001431	NS05	Invoice numbers ending with 29, 30 are missing
NS05060100001435	NS05	Invoice numbers ending with 32, 33, 34  are missing

I need to find out all the missing InvoiceNumber by PlantCode. So, for the example above, the outcome I am looking for is:

MissingInvoices	PlantCode
NS05060100001422	NS05
NS05060100001425	NS05
NS05060100001426	NS05
NS05060100001427	NS05
NS05060100001429	NS05
NS05060100001430	NS05
NS05060100001432	NS05
NS05060100001433	NS05
NS05060100001434	NS05

Could anyone please help how do I go about identifying the missing invoices by each plant?

Regards,

Bhaskar

[sunny_talwar]

Something along these lines

INV_TABLE:

LOAD InvoiceNumber,

     PlantCode,

     [Pstng Date],

     Right(InvoiceNumber, 12) as InvNum

FROM

[missing invoice.xls]

(biff, embedded labels, table is Sheet1$);

Concatenate(INV_TABLE)

LOAD PlantCode,

PlantCode & Num(Temp, '000000000000') as InvoiceNumber

Where not Exists(InvoiceNumber, PlantCode & Num(Temp, '000000000000'));

LOAD PlantCode,

Min + IterNo() - 1 as Temp

While Min + IterNo() - 1 <= Max;

LOAD PlantCode,

Min(InvNum) as Min,

Max(InvNum) as Max

Resident INV_TABLE

Group By PlantCode;