How to filter duplicates

[Pako]

HI

I am looking for a way to identify duplicate records as follows: In the id column, there are repeated "Ids" that are assigned to different names. The solution I am looking for is that the "Result" column only shows the repeated records that are not assigned to Ivan and those unique records regardless of who they are assigned to.

Id	assigned	RESULT
2022-000358_F01-A01	jose	Jose
2022-000358_F01-A01	Ivan	-
2021-000341_F01-A01	Ana	Ana
2021-000341_F01-A01	Ivan	-
2021-000341_F02-A01	Frank	Frank
2021-000341_F02-A01	Ivan	-
2021-000341_F03-A01	Jose	Jose
2021-000341_F03-A01	Ivan	-
2021-000341_F04-A01	Ivan	-
2021-000341_F04-A01	Jose	Jose
2021-000341_F05-A01	Ivan	-
2021-000341_F05-A01	Ana	Ana
2021-000341_F06-A01	Ivan	-
2021-000341_F06-A01	Frank	Frank
2022-000773_F01-A01	Ivan	Ivan
2022-000773_F02-A01	Ivan	Ivan

Thanks for your help

[Pako]

HI

I am looking for a way to identify duplicate records as follows: In the id column, there are repeated "Ids" that are assigned to different names. The solution I am looking for is that the "Result" column only shows the repeated records that are not assigned to Ivan and those unique records regardless of who they are assigned to.

Id	assigned	RESULT
2022-000358_F01-A01	jose	Jose
2022-000358_F01-A01	Ivan	-
2021-000341_F01-A01	Ana	Ana
2021-000341_F01-A01	Ivan	-
2021-000341_F02-A01	Frank	Frank
2021-000341_F02-A01	Ivan	-
2021-000341_F03-A01	Jose	Jose
2021-000341_F03-A01	Ivan	-
2021-000341_F04-A01	Ivan	-
2021-000341_F04-A01	Jose	Jose
2021-000341_F05-A01	Ivan	-
2021-000341_F05-A01	Ana	Ana
2021-000341_F06-A01	Ivan	-
2021-000341_F06-A01	Frank	Frank
2022-000773_F01-A01	Ivan	Ivan
2022-000773_F02-A01	Ivan	Ivan

Thanks for your help

[Aditya_Chitale]

try this :

test:
LOAD * INLINE [
id, assigned
2022-000358_F01-A01, jose
2022-000358_F01-A01, Ivan
2021-000341_F01-A01, Ana
2021-000341_F01-A01, Ivan
2021-000341_F02-A01, Frank
2021-000341_F02-A01, Ivan
2021-000341_F03-A01, Jose
2021-000341_F03-A01, Ivan
2021-000341_F04-A01, Ivan
2021-000341_F04-A01, Jose
2021-000341_F05-A01, Ivan
2021-000341_F05-A01, Ana
2021-000341_F06-A01, Ivan
2021-000341_F06-A01, Frank
2022-000773_F01-A01, Ivan
2022-000773_F02-A01, Ivan
];

left join (test)

test_2:
load
assigned,
count(assigned) as totalcount
resident test group by assigned;

In frontend table add this expression:

if(assigned='Ivan',null(),if(totalcount>1,assigned))

Regards,

Aditya

[Pako · Accepted solution]

Hi

Thanks for your answer, but i found the solution in this way.

load "Id Plan Acción",
if(count(Id)>1,'Duplicate','Unique') as Check resident Data1 group by id;

left join
Load Check,
assigned,
id,
if(Check='Duplicate' and assigned<>'ivan',assigned,if(Check='Unique', assigned)) as Result resident Data1;