removing duplicate records on load

[#1]

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.

[mdmukramali]

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]

hi,

Check this:

http://community.qlik.com/message/617386#617386

http://community.qlik.com/message/560159#560159

Wish you a very happy new year

Thanks,
AS

[#4 · Accepted solution]

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.

[Anonymous]

This is not Duplicated Data ,

if you want to remove duplicated Rows Use Distinct function.

[#6]

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

[#8]

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

[#9]

Thanks to all of you for your help.

wish you all a very very happy new year

Regards,

JKV