topic Re: Delete dublicate rows group by some field in loadscript in QlikView

Delete dublicate rows group by some field in loadscript

Anonymous — Mon, 22 Jun 2015 16:00:48 GMT

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.

Re: Delete dublicate rows group by some field in loadscript

Mon, 22 Jun 2015 16:12:15 GMT

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;

Re: Delete dublicate rows group by some field in loadscript

Anonymous — Mon, 22 Jun 2015 16:31:44 GMT

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

Re: Delete dublicate rows group by some field in loadscript

Mon, 22 Jun 2015 17:08:19 GMT

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;

Re: Delete dublicate rows group by some field in loadscript

maxgro — Mon, 22 Jun 2015 17:26:09 GMT

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;

Re: Delete dublicate rows group by some field in loadscript

Anonymous — Mon, 22 Jun 2015 18:39:10 GMT

Thank you!

it's what i need.