deduplication

[#1]

Hi I hope someone will be able to help me.

I need to deduplicate some data, i have attached my code below, basically i need to exclude PolicyId's that are shown more than once and completely exclude a customer that has a posting date in September.

I hope that makes sense, i look forward to some help on this.

Graham

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Customers:

LOAD
[Days since invoice],

Duplication?,

PolicyID,

[Customer Salutation],

[Customer Address Line 1],

[Customer Address Line 2],

[Customer Address Line 3],

[Customer Post Town],

[Customer Post County],

[Customer Post Code],

[Posting Date],

Month([Posting Date]) as PostingMonth

,

Year([Posting Date]) as PostingYear

,[INVOICE COUNT]

FROM[\\EX-FPS01\HomeDrives$\Grahamw\sarah data filtered.xls]

(

biff, embedded labels, table is Sheet1$);

[#2]

Are you saying you want to remove every row where a policyID is duplicated anywhere or simply remove any after the first occurrence of the policyID?

[prieper]

Hi,
not quite clear, what do you wish to achieve. If there might be some duplicates (over all fields) LOAD DISTINCT might help you. Otherwise you may crawl through all records and analyse with PEEK()-function, if the previous key is the same as the current and then flag the current record.

HTH
Peter

[#4]

sorry it is not easy to explain.

the spreadsheet has a list of customers, the policyID is shown every time an invoice has been paid it also shows the date it was paid.

I need to dedupe customer that are shown more than once and if they have a date paid in september then i want to delete any duplicate of that customer, basically i dont want the danger of writing to a customer that has claimed in september.

From what i have been reading the load distinct might do the job but i simply dont know much on the side of scripting so i dont know how to write it in the script or expression.

Sorry for the lack of basic knowledge guys.

Graham