Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

removing duplicate records on load

Hi,

i have a table two columns  1) ClientId and 2) ClientType

there are some cases the same ClientId appears with different ClientType also e.q

         ClientId ,       ClientType         

1,      ABC      ,          10

2,      ABC      ,          20

3,      XYZ       ,          10

i want to remove the duplicate ClientId on the basis of which ClientType i prefer to keep and which not.

Kindly help.

1 Solution

Accepted Solutions
Not applicable
Author

Add a Where clause at the end of the LOAD

WHERE not exists(Client_id) AND (ClientType <> whateveryouwant)

or you can include a more complex condition to discard rows at load time.

View solution in original post

8 Replies
mdmukramali
Specialist III
Specialist III

Dear,

on Which condition you want to remove the duplicates Records.

ClientType=10 or ClientType=20 ?

can you explain bit more

Thanks,

Mukram

amit_saini
Master III
Master III

Not applicable
Author

Add a Where clause at the end of the LOAD

WHERE not exists(Client_id) AND (ClientType <> whateveryouwant)

or you can include a more complex condition to discard rows at load time.

View solution in original post

asimansari2017
Contributor III
Contributor III

This is not Duplicated Data ,

if you want to remove duplicated Rows Use Distinct function.

Not applicable
Author

Currently that not fixed thats why i did explain more that will be finalized later...

its_anandrjs

Hi,

There are many ways for this but what is your expected out put here you can also create a flag field also to find duplicates and in front end remove them by using SET analysis.

Source:
LOAD ClientId,ClientType,RowNo() as Rid;
LOAD * INLINE [
ClientId, ClientType
ABC, 10
ABC, 20
XYZ, 10
]
;

CountTable:
LOAD
ClientId,
Count(ClientId) as Count
Resident Source
Group By ClientId;

Info:
LOAD
ClientId, Rid,
Count,
If(Count > 1,'Duplicate Records','No Duplicates') as Details
Resident CountTable;
DROP Table CountTable;

Regards

Anand

Not applicable
Author

As per the record level is it not duplicate, but as per ClientId level it is duplicate. As per the business rules a ClientId can be belongs to any specific ClientType only. but due to there is no Validation check and there is no any master regarding ClientType this is happening and i have to finalized the Master for ClientType and ClientId & ClientType Relationship.

Thanks

JKV

Not applicable
Author

Thanks to all of you for your help.

wish you all a very very happy new year

Regards,

JKV