List a field based on possible values in another column

[#1]

Hello All,

              I need helping doing something similar to

select name from table having count(dept) >1 and dept like '123' in Qlikview.

For e.g. if I have the below:

name   dept  status

a          123    active

b          123    active

b          456    in-build

c          456    active

c          567    in-build

d          967    in-build

d          789   retired

e         456     active

I want to list all names that have a count(dept) >1 ( which in my case would be b ,c and d) and where one of the dept values for that name is 456.

So my output in this case should be

b

c

Basically I am trying to find duplicate rows and filter the duplicates based on certain values in the dept field.

Thanks,
Samira

[#2]

Thank you. That worked! Not sure what I was missing before. This is exactly what I was trying to get for the output.