topic deduplication in QlikView

deduplication

Wed, 07 Oct 2009 23:39:23 GMT

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$);

deduplication

Thu, 08 Oct 2009 02:19:59 GMT

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?

deduplication

prieper — Thu, 08 Oct 2009 04:34:28 GMT

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

deduplication

Thu, 08 Oct 2009 05:06:29 GMT

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