Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andreyfcdk91
New Contributor III

Delete dublicate rows group by some field in loadscript

Good day!

In my model i have following fields:

rowno(),

Id,

Name,

Customer_id

Main_field.

My result table:

     

rowno()IdNameCustomer_idMain_field
122Name1196Main1
222Name2196Main1
322Name1196Main1
422Name2197Main1

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

    

rowno()IdNameMain_field
122Name1Main1
222Name2Main1

Is it possible?

Model and source file in attachment.

Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: Delete dublicate rows group by some field in loadscript

RESULT

1.png

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;

5 Replies
Not applicable

Re: Delete dublicate rows group by some field in loadscript

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;

andreyfcdk91
New Contributor III

Re: Delete dublicate rows group by some field in loadscript

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

Not applicable

Re: Delete dublicate rows group by some field in loadscript

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;

MVP
MVP

Re: Delete dublicate rows group by some field in loadscript

RESULT

1.png

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;

andreyfcdk91
New Contributor III

Re: Delete dublicate rows group by some field in loadscript

Thank you!

it's what i need.

Community Browser