topic Re: Duplicate records for LoginID in QlikView

Duplicate records for LoginID

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi experts!

I need help on how to go about this scenario in the script or expression.

If there are duplicate records identified with a LoginID, select the record with Active (A) status,

if none, which gives records of Terminated (T) status, then select the record based on the latest EmpID.

EmpID	LoginID	Status
342	1	T
453	1	A	This record should be selected first
585	2	T
732	2	T
843	2	T	This should be selected, if status has no 'A'

Appreciate your help.

Re: Duplicate records for LoginID

arvind_patil — Fri, 27 Oct 2017 05:38:06 GMT

HI Preciosa,

Please find below code as well as attachment:

Data:

Load * Inline [

EmpID, LoginID, Status

342, 1, T

453, 1, A

585, 2, T

732, 2, T

843, 2, T];

NoConcatenate

FinalData:

LOAD Max(EmpID) as LatestEMPID,LoginID,Status

Resident Data Group By LoginID,Status ;

DROP Table Data;

Thanks,

Arvind Patil

Re: Duplicate records for LoginID

xarapre7 — Fri, 27 Oct 2017 05:51:36 GMT

Hi Arvind!

I don't see any attachment.

Re: Duplicate records for LoginID

techvarun — Fri, 27 Oct 2017 06:01:31 GMT

Duplicate records for LoginID

Click here to see the attachment. you won't be able to see it on the inbox

Re: Duplicate records for LoginID

xarapre7 — Fri, 27 Oct 2017 06:18:38 GMT

i tried it in a table and it gave 3 results.

LoginID Status LatestEmpID

1 A 453

1 T 342

2 T 843

There should only be 2 records

LoginID Status LatestEmpID

1 A 453

2 T 843

Thanks in advance!

Re: Duplicate records for LoginID

Sergey_Shuklin — Fri, 27 Oct 2017 06:26:25 GMT

Hello!

Because there is grouping on two fields Login & Status.

If you use this syntax:

Data:

Load * Inline [

EmpID, LoginID, Status

342, 1, T

453, 1, A

585, 2, T

732, 2, T

843, 2, T];

NoConcatenate

FinalData:

LOAD Max(EmpID) as LatestEMPID,max(LoginID) as LoginID,Status

Resident Data Group By Status ;

DROP Table Data;

You will get wanted result. But there is still a question - is it correct to use max() function for LoginID?

Re: Duplicate records for LoginID

techvarun — Fri, 27 Oct 2017 06:38:50 GMT

Test:
LOAD *,If(Status = 'A',1,0) as Flag INLINE [
    EmpID, LoginID, Status
    342, 1, T
    453, 1, A
    585, 2, T
    732, 2, T
    843, 2, T
];


left Join(Test)
 Load LoginID,Sum(Flag) as Field,Max(EmpID) as Key Resident Test Group by LoginID;

 left Join(Test)
 Load  if(Flag >=1 AND Status ='A', 1, If( Flag = 0 And EmpID = Key, 1)) as NewFlag, EmpID resident Test; 

 Load EmpID,LoginID,Status Resident Test where NewFlag =1; DROP Table Test;

Try the Above script

Re: Duplicate records for LoginID

xarapre7 — Fri, 27 Oct 2017 06:45:43 GMT

This gave the expected result! Thank you!!!

Re: Duplicate records for LoginID

xarapre7 — Fri, 27 Oct 2017 06:46:14 GMT

This gave the expected result as well!

Thank you very much!