To remove duplicate rows from two column

[Anonymous]

Hi Everyone,

I have a requirement which says to remove the duplicates from the column - "Bill to Customer Name and Customer Part Number" and then load the data.  I tried to wrote the below code but it didn't work form. Can you help me with the code. Attach is the excel sheet.

[Sheet1]:

LOAD Distinct *

FROM [lib://Documents/ODS.SIS.MKT.00019 - Open Quote Details (1).xlsx]

(ooxml, embedded labels, table is Sheet1) Where NOT Exists([Customer Part Number],[Bill To Customer Name]);

Thanks in advance!

Thanks and Regards,

Drishti Goel

[felipedl]

Hi Drishti,

Your data isn't duplicated for the field you mentioned, each line has at least one different column for the fields you have on the spreadsheet.

Your script is trying to check the duplicates for the entire line and the not exists, as you've put it, won't have any effect on your data.

Let's say you get the following fields from your file:

Branch

Bill To Customer EB Name

Bill To No

Bill To Customer Name

End Customer Number

End Customer Name

ISR Name

FSR Name

Quote Header Creation Date

Quote Expiration Date

Quote Number

Type

Customer Part Number

Equal lines

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

04/01/2018

03/02/2018

21103557

Firm

1

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

04/01/2018

03/02/2018

21103557

Firm

1

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

30/01/2018

01/03/2018

21177545

Firm

2

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

30/01/2018

01/03/2018

21177545

Firm

2

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

30/01/2018

01/03/2018

21180604

Firm

3

Denver

US_CA_Halifax_Advantage_Denver

2171172

Vais Technology

Peter-Paul, Melanie

Marsh, Millie

30/01/2018

01/03/2018

21180604

Firm

3

The distinct will get, from the 6 rows above, only 3 (three) lines.

The rest of your data for this 6 rows is differnent for each row in the spreadsheet, so to get the distinct values, you'll have to specify some fields, as I did above.

Felipe.

[Anonymous]

Hi Felip,

Thanks for your reply.

I understand that every row has different values and if I'm putting distinct then its gonna fetch all the 6 rows. So here in this case, ideally I should not use distinct. But my actual goal is to load the data and put the filter on ([Customer Part Number],[Bill To Customer Name]) so that I can remove the duplicated data. How can I achieve this?

Thanks and Regards,

Drishti Goel

[Anonymous]

Any lead on the solution will be highly appreciated