Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Removing the duplicates

Profile IDStatusCustId
780INPROG100
88INPROG200
789INPROG200
800INPROG300

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 IDCustIdStatus
780100Complt
800300Cancel
12 Replies
MVP
MVP

Re: Removing the duplicates

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

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;

Capture.PNG

Not applicable

Re: Removing the duplicates

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.

Not applicable

Re: Removing the duplicates

hi sunny

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

thanks& regards ,

ravi

Not applicable

Re: Removing the duplicates

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
Honored Contributor

Re: Removing the duplicates

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

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;

Not applicable

Re: Removing the duplicates

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;

Not applicable

Re: Removing the duplicates

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

Community Browser