topic Re: Removing the duplicates in QlikView

Removing the duplicates

Sun, 13 Dec 2015 16:19:02 GMT

Profile ID	Status	CustId
780	INPROG	100
88	INPROG	200
789	INPROG	200
800	INPROG	300

Here the above given table has duplicate customer ID(200).

While loadind data from the table I would like to have the result like below by removing the duplicates completely.

Output:
Profile ID	CustId	Status
780	100	Complt
800	300	Cancel

Re: Removing the duplicates

maxgro — Sun, 13 Dec 2015 17:22:55 GMT

s2:

LOAD [Profile ID],

Status,

CustId

FROM

[https://community.qlik.com/thread/196425]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (s2)

load CustId, count(CustId) as Cnt

Resident s2 group by CustId;

Right Keep (s2)

LOAD CustId

Resident s2

Where Cnt=1;

DROP Field Cnt;

Re: Removing the duplicates

sunny_talwar — Sun, 13 Dec 2015 21:41:45 GMT

You can also try this:

Table:

LOAD * Inline [

Profile ID, Status, CustId

780, INPROG, 100

88, INPROG, 200

789, INPROG, 200

800, INPROG, 300

];

Right Join (Table)

LOAD *

Where Count = 1;

LOAD CustId,

Count(Status) as Count

Resident Table

Group By CustId;

Re: Removing the duplicates

Tue, 01 Mar 2016 10:01:46 GMT

Hi reddy,

I am also facing same problem.

u get the correct answer can u please mark as a answer otherwise can u have post the answer.

thanks

ravi.

Re: Removing the duplicates

Tue, 01 Mar 2016 10:03:28 GMT

hi sunny

I didn't get u r answer can u pls explain briefly...

thanks& regards ,

ravi

Re: Removing the duplicates

Sun, 01 May 2016 09:37:28 GMT

Hi sunny,

in above example I need this output.

profile id Status Cust id

780 inprog 100

88 inprog 200

800 inprog 300

what are the change I do in script.

Regards,

ravi

Re: Removing the duplicates

trdandamudi — Sun, 01 May 2016 10:02:42 GMT

The below should give you want you want:

Table:

LOAD * Inline [

Profile ID, Status, CustId

780, INPROG, 100

88, INPROG, 200

789, INPROG, 200

800, INPROG, 300

];

NoConcatenate

Final:

Load *

Where Count =1;

Load

[Profile ID],

Status,

CustId,

If(CustId<>Previous(CustId),1,0) as Count

Resident Table;

Drop Table Table;

Re: Removing the duplicates

sunny_talwar — Sun, 01 May 2016 12:46:25 GMT

This is the other option using the Right join:

Table:

LOAD * Inline [

Profile ID, Status, CustId

780, INPROG, 100

88, INPROG, 200

789, INPROG, 200

800, INPROG, 300

];

Right Join (Table)

LOAD CustId,

FirstValue([Profile ID]) as [Profile ID]

Resident Table

Group By CustId;

Re: Removing the duplicates

Sun, 01 May 2016 14:29:06 GMT

Temp:

Load * Inline [

Profile ID, Status, CustId

780, INPROG, 100

88, INPROG, 200

789, INPROG, 200

800, INPROG, 300

];

Inner Join(Temp)

Count_Cust:

Load CustId

where CountID = 1;

Load CustId,count(CustId) as CountID

Resident Temp

group by CustId;

Re: Removing the duplicates

Fri, 06 May 2016 08:46:27 GMT

Hi Thirumala,

Thnka you for the script.....

But this script works some records only........by using this script some records its deleting fst records and some time its removing scond records......

Re: Removing the duplicates

sunny_talwar — Fri, 06 May 2016 08:53:53 GMT

Did you look at my response here:

https://community.qlik.com/message/1033667#986054

Re: Removing the duplicates

Fri, 06 May 2016 09:33:15 GMT

Hi sunny,

Exact my problem is......

I have data like this......1000 records

sample data:

Barcode create_date prepared _By

123 24-04-2016 ravi

124 24-04-2016 teja

123 29--04- 2016 ---

345 26-04-2016 ----

346 26-04-2016 ---

345 29-04-2016 ---

I want these outputs:

0utput1:

Barcode create date prepared _By

123 24-04-2016 ravi

124 24-04-2016 teja

345 26-04-2016 ----

346 26-04-2016 ---

In above output using where condition prepared _By =''--' output2 will come no problem.

0utput2:

Barcode create date prepared _By

345 26-04-2016 ----

346 26-04-2016 ---

my main problem is output1 is not getting perfectly while using Thirumal script....its workinh some records its removing fst record and some times second records its removing.....In duplicate want remove only seconds time reapted records.

Re: Removing the duplicates

rubenmarin — Fri, 06 May 2016 11:06:18 GMT

Hi raviteja, your request is different from the original poster, he wanted to remove all duplicates while you want to keep the first one.

To achieve that you only need to use exist(), ie:

LOAD * FROM [Sample_Data] where not Exists(Barcode)

If Barcode was loaded in previous tables you need to change the field name to check:

LOAD *, Barcode as BarcodeTmp FROM [Sample_Data] where not Exists(BarcodeTmp);

DROP Field BarcodeTmp;