Removing the duplicates

[#1]

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

[maxgro]

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;

[sunny_talwar]

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;

[#4]

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.

[#5]

hi sunny

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

thanks& regards ,

ravi

[#6]

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

[trdandamudi]

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;

[sunny_talwar]

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;

[#9]

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;

[#10]

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......