How to deduplicate and select first items of a table

[ktt672]

Hi I am still a newbie and working on a project of antibiotics survailance program using Qlik Sense and have a hiccup, I wish somebody can help...

The requirement from doctors wishes to have flexibility to deduplicate the table so that only show the

first organism isolates and their antibiotics per episode, e.g.

Before changes

#	Episode No.	Time of collection	Organisms	Antibiotics	Result
1	12345	22/8/2020 12:30	S. aureus	Cefoxitin	Resistant
2	12345	22/8/2020 12:30	S. aureus	Gentamicin	Sensitive
3	12345	23/8/2020 04:30	E. Coli	Amikacin	Intermediate
4	12345	24/8/2020 09:20	E. Coli	Ampicillin	Sensitive
5	12345	24/9/2020 09:30	S. aureus	Cefoxitin	Sensitive
6	12345	24/9/2020 09:30	S. aureus	Penicillin	Sensitive
7	23456	24/9/2020 12:30	P. aeruginosa	Amikacin	Intermediate

Since S. aureus in 12345 has done the first one in 23/8, # 5 and 6 are removed;

Since E coli  in 12345 has done the first one in 24/8, #4 is removed;

Thus,

After changes

#	Episode No.	Time of collection	Organisms	Antibiotics	Result
1	12345	22/8/2020 12:30	S. aureus	Cefoxitin	Resistant
2	12345	22/8/2020 12:30	S. aureus	Gentamicin	Sensitive
3	12345	23/8/2020 04:30	E. Coli	Amikacin	Intermediate
7	23456	24/9/2020 12:30	P. aeruginosa	Amikacin	Intermediate

How to write an expression in sheets or in data load editor to do this thing? She wants to have flexibility to see all and deduplicated data. Thanks!

[rbartley · Accepted solution]

This might be a stupid question, but have you thought of having 2 tables (one showing duplicates and one not) and including both in a container so that the user can choose which one to see?