Delete duplicates in Error table

[Applicable88]

Hello,

I have something like this:

Errornumber	Date	Description	Material	Amount material involved	Status
1234567	01.01.2020					closed
1234567	01.01.2020					open
9999999	02.01.2020					closed
9999999	02.01.2020					open
1234567	05.01.2020					closed
6666655	06.01.2020					closed
6666666	07.01.2020					closed
4666666	08.01.2020					closed

As you can see the dataset with the error report number 1234567 and 9999999 both have a status open and a status close. This is because if a case is not closed there are still a duplicate of that case in "open". When its closed I only have one value. since I want to show which errors are still not solved I want to remove the "close" duplicates of these cases which have a pedant which is still in "open" 

Note I can't use distinct, because otherwise I willl lose this dataset and cannot use another linked KPI window where I calculate the difference of open to close cases. I don't want to remove all close cases, I just want to remove the close cases of these report numbers which have a open case. Otherwise the "problem count" is false. 

Hope someone can help.

Thanks in advance.

Best 

[Anil_Babu_Samineni · Accepted solution]

Design:

If(Index(Concat(TOTAL <Date> DISTINCT Status, ','), 'open'), 'open', Status)

Script:

You already have one solution by @ArnadoSandoval , May be some optimized script below

Table:
LOAD Errornumber,
Date,
Description,
Material,
[Amount material involved],
F7 as Status
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Delete-duplicates-in-Error-table/m-p/1760556#M174914]
(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)
Load Date, Concat(Status, ',') as Status_Copy
Resident Table Group By Date;

Final:
NoConcatenate
LOAD *, If(Index(Status_Copy,'open'), 'open', Status_Copy) as Expected
Resident Table;

Drop Table Table;

[Anil_Babu_Samineni]

@Applicable88  Please check once and close the thread, If you feel that you got what you are expecting?

[Applicable88]

Thanks @Anil_Babu_Samineni !

[shopia4342]

Thanks for the update and quick reply. I'll be sure to keep an eye on this thread.

Albany NY search engine optimization