How to detect duplicate?

[Anonymous]

Employee                Status                         StatusId

Alex                         Submit                             A1

Alex                         Submit                              A2

Alice                         In progress                        B1

Jack                         Submit                              C1

Jack                         In progress                        C2

What formula should i use,I want to show If employee has more than one status then it is duplicate and show the duplicate id.

The result should be:

Employee   Status                          Duplicate ID

Alex           Duplicate                         A1,A2

Jack           Duplicate                         C1,C2

TQ

[SunilChauhan]

see the attched file

hope this helps

[sujeetsingh]

Sunil has given you the sample file ..

Just use group by in qlikview script

[Anonymous]

Hi,

Use group by Employee and get count(Status)>1

HTH

Ravi N.

[jagan · Accepted solution]

Hi,

Try like this

Dimension : Employee

Expression :

Status ----- If(Count(Distinct StatusId)>1, 'Status')

Duplicate ID----- If(Count(Distinct StatusId)>1, Concat(StatusId, ','))

Hope this helps you.

Regards,

Jagan.

[#6]

See the attachment hope it helps

[anbu1984]

Tab:

Load * inline [

Employee    ,            Status          ,               StatusId

Alex            ,             Submit     ,                        A1

Alex                 ,        Submit       ,                       A2

Alice               ,          In progress  ,                      B1

Jack                 ,        Submit      ,                        C1

Jack                  ,       In progress    ,                    C2

];

Final:

NoConcatenate

Load * Where Dup like '*,*';

Load Employee,'Duplicate' As Status,Concat(StatusId,',') As Dup Resident Tab Group by Employee;

Drop table Tab;

[#8]

try this

[#9]

try this

Status:-if(Count(DISTINCT StatusId)>1, 'duplicate',Concat(Status,','))

Duplicate Id:-if( count( distinct StatusId)>1, concat(StatusId,','),StatusId)

see the attachment

[SunilChauhan]

whats wrong with my attachedment?