topic Re: removing duplicate records on load in QlikView

removing duplicate records on load

Thu, 01 Jan 2015 07:41:55 GMT

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.

Re: removing duplicate records on load

mdmukramali — Thu, 01 Jan 2015 07:55:32 GMT

Dear,

on Which condition you want to remove the duplicates Records.

ClientType=10 or ClientType=20 ?

can you explain bit more

Thanks,

Mukram

Re: removing duplicate records on load

amit_saini — Thu, 01 Jan 2015 07:58:29 GMT

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

Re: removing duplicate records on load

Thu, 01 Jan 2015 08:09:34 GMT

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.

Re: removing duplicate records on load

Anonymous — Thu, 01 Jan 2015 08:32:33 GMT

This is not Duplicated Data ,

if you want to remove duplicated Rows Use Distinct function.

Re: removing duplicate records on load

Thu, 01 Jan 2015 08:33:37 GMT

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

Re: removing duplicate records on load

its_anandrjs — Thu, 01 Jan 2015 08:35:36 GMT

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

Re: removing duplicate records on load

Thu, 01 Jan 2015 08:37:44 GMT

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

Re: removing duplicate records on load

Thu, 01 Jan 2015 08:43:27 GMT

Thanks to all of you for your help.

wish you all a very very happy new year

Regards,

JKV