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

[Anonymous]

See attached, hope I got your requirements right...

[ramoncova06]

This can be added in a listbox or set analysis

=AGGR(only({$

<dept = {'456'}>

*

<rname={"=count(dept)>1"}>

} rname),rname)

[anbu1984]

=Concat(If(Aggr(Count(dept),name)>1 And Aggr(Count({<dept = {'456'}>}name),name)=1 ,name),Chr(10))

[MK_QSL]

If you want to show in Text Box use below...

=CONCAT({<name = {"=COUNT({<name = p({<dept = {'456'}>}name)>}DISTINCT dept)>1"}>}DISTINCT name,'|')

if you want to show in List Box use below as an Expression to create a list box.

=Aggr(Only({<name = {"=COUNT({<name = p({<dept = {'456'}>}name)>}DISTINCT dept)>1"}>}name),name)

[#6]

Michael,

            Thank you for your response. The solution gives me the output I want. Is it possible to do same in a list box instead?

Thanks,

Samira

[#7]

Hello Anbu,

                 This is listing all the departments but not the names from the duplicate rows which match the condition.

Thanks,
Samira

[#8]

Manish,

            I tried both your solutions. The Text Box solution is not displaying anything. The List Box solution is listing the departments and not the names from the duplicate rows.

Thanks,

Samira

[Anonymous]

Sure.  See the attached.

[anbu1984 · Accepted solution]

Check this app