Delete dublicate rows group by some field in loadscript

[Anonymous]

Good day!

In my model i have following fields:

rowno(),

Id,

Name,

Customer_id

Main_field.

My result table:

rowno()	Id	Name	Customer_id	Main_field
1	22	Name1	196	Main1
2	22	Name2	196	Main1
3	22	Name1	196	Main1
4	22	Name2	197	Main1

What i need: Get result table, which has no dublicate rows by Customer_Id:

rowno()	Id	Name	Main_field
1	22	Name1	Main1
2	22	Name2	Main1

Is it possible?

Model and source file in attachment.

Thanks.

[#2]

You can't use both DISTINCT & ROWNO() in same load statement.

Hi Try like Below:

Temp:

load Distinct

  Id,

  Name,

  Main_field

FROM Test.xlsx (ooxml, embedded labels, table is Sheet1) ;

Table:

LOAD RowNo() , * Resident Temp;

DROP Table Temp;

[Anonymous]

For what i wrote this post:

my task is to concat different names for my Id.

Table:

load

*

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

New_table:

load

Id,

Concat(Name,'-') as NewName

Resident Table

Group by Id;

DROP Table Table;

In result i get NewName Name1-Name1-Name2-Name2, but i need: Name1-Name2

[#4]

Use Distinct Qualifier Inside Concat function like below:

Table:

load

*

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

New_table:

load

Id,

Concat(DISTINCT Name,'-') as NewName

Resident Table

Group by Id;

DROP Table Table;

[maxgro · Accepted solution]

RESULT

SCRIPT

Table:

load

rowno() as rn,

*

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

FinalTable:

load *, RowNo();

NoConcatenate

load Id, Customer_id, Name, Main_field

Resident Table

where Customer_id <> Peek(Customer_id)

order by Customer_id, rn;

DROP Table Table;

[Anonymous]

Thank you!

it's what i need.