how to remove duplicates from table

[#1]

Hi All,

How to remove duplicates from table where multiple keys are involved.

In my case , I want to remove duplicates where [Main Project Name],  SKUID and [PMaterial Number] is same.

LOAD   

     [Main Project Name],

     [Project Status],

     SKUID,

     [SKU Status],

     [PMaterial Number],

     [PMaterial Status]

FROM

Project_list_status.xlsx

(ooxml, embedded labels, table is Sheet1);

Can you please help.

Thanks in advance.

[#2]

attached qvd file.it is showing two records.

[Anonymous · Accepted solution]

TABLE:

LOAD

     Distinct

     [Main Project Name],

     [Project Status],

     [Project Start Date],

     SKUID,

     [SKU Description],

     [SKU Status],

     [PMaterial Number],

     [PMaterial Status],

     date

FROM

[data.qvd]

(qvd);

STORE TABLE into distinct.qvd (qvd);

[parul_mehta]

Hi Dipak , try Robin Hausdörfer solution, it is working for me.

[evan_kurowski]

Hello Dipak,  give this a try

//This should be a DISTINCT list of %KEY_UNIQUE_SUBSET_OF_FIELDS
[UNIQUE_SUBSET_OF_FIELDS]:
LOAD DISTINCT
AutoNumberHash128([Main Project Name],  SKUID,  [PMaterial Number]) AS %KEY_UNIQUE_SUBSET_OF_FIELDS,
[Main Project Name],
[PMaterial Number],
SKUID
FROM
Project_list_status.xlsx; 

//These rows can be a 1 to many per %KEY_UNIQUE_SUBSET_OF_FIELDS
[NON_UNIQUE_ATTRIBUTES]:
LOAD  
AutoNumberHash128([Main Project Name],  SKUID,  [PMaterial Number]) AS %KEY_UNIQUE_SUBSET_OF_FIELDS,
[SKU Status],
[Project Status],
[PMaterial Status]
FROM
Project_list_status.xlsx;

[#6]

Thanks to all .it is working for me.Distinct keyword is checking all the field combination and maintaining unique record in qvd file as well as during display in qlikview. Thanks to all.