Duplicate records for LoginID - Qlik Community

xarapre7 · ‎2017-10-27

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.

.

arvind_patil · ‎2017-10-27

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

xarapre7 · ‎2017-10-27

Hi Arvind!

I don't see any attachment.

techvarun · ‎2017-10-27

Duplicate records for LoginID

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

xarapre7 · ‎2017-10-27

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!

Sergey_Shuklin · ‎2017-10-27

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?

techvarun · ‎2017-10-27

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

xarapre7 · ‎2017-10-27

This gave the expected result! Thank you!!!

xarapre7 · ‎2017-10-27

This gave the expected result as well!

Thank you very much!