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]

What is the outcome you are expecting?

[Applicable88]

Hi,

as you can see if I would just count all numbers I would get 8.

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

I want to disregard those "closed" cases which still has a "open" case going on. I don't want to see the crossed out dataset, I marked in the table. 

Seemingly the Errortable produce to every "open" case already a "closed" one. Normally that wouldnt make sense because they share the same error ID. So I'm asking if there is a possbility to remove the "closed" case of a "open" case as long as it is still "open"? As far I can see it, when a case is closed then at least the open will vanish automatically. What interest me is to remove the duplicate as long as the case is open. 

[Anil_Babu_Samineni]

Hi, Sorry It took longtime. Are you waiting still or resolved?

[Applicable88]

Hello @Anil_Babu_Samineni ,

yes would still like to know. Better than never!

Do you know a solution?

Still thank you for reaching out.

Best

[thi_pham]

By a table , I  could achieve your expected result by:

- As a measure : rowno(). Set Show column if = 0 -> this is a hidden column

- Set sorting by column status: Alphabetically -> Descending -> Open is sorted up

- Set Limitation on status = Fixed number : Top 1.

Hope it helps

[ArnadoSandoval]

Hi @Applicable88 

This script does the job; I did not drop the temporary work tables because I was checking the results on the UI

NoConcatenate 

ED:
Load Errornumber &'|'& Date &'|'& Description &'|'& Material &'|'& [Amount material involved] As %Record,
     *
;
Load * Inline [
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
];

NoConcatenate

ED_C:
Load [%Record] As key,
     Count([%Record]) As count
Resident ED 
Group By
     [%Record];

Map_ED_C:
Mapping Load key,
     count
Resident ED_C; 

NoConcatenate

Result:
Load Errornumber                   as r_Errornumber,
     Date                          as r_Date,
     Description                   as r_Description,
     Material                      as r_Material,
     [Amount material involved]    as [r_Amount material involved],
     Status                        as r_Status
Resident ED
Where ApplyMap('Map_ED_C', [%Record])  = 1
Or  ( ApplyMap('Map_ED_C', [%Record])  = 2 and Status <> 'closed' )
;

Exit Script;

The screenshot below shows the results!

Hope this helps,

[Anil_Babu_Samineni]

Do you need in script or design? Design quite so simple 

[Applicable88]

@Anil_Babu_Samineni can you show me both scenarios?

Thx.

[Emmaleen]

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