topic Re: List a field based on possible values in another column in QlikView

List a field based on possible values in another column

Mon, 13 Apr 2015 21:30:51 GMT

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

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

Thanks,
Samira

Re: List a field based on possible values in another column

Anonymous — Mon, 13 Apr 2015 21:52:43 GMT

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

Re: List a field based on possible values in another column

ramoncova06 — Mon, 13 Apr 2015 22:14:17 GMT

This can be added in a listbox or set analysis

=AGGR(only({$

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

} rname),rname)

Re: List a field based on possible values in another column

anbu1984 — Tue, 14 Apr 2015 06:22:45 GMT

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

Re: List a field based on possible values in another column

MK_QSL — Tue, 14 Apr 2015 06:49:19 GMT

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)

Re: List a field based on possible values in another column

Tue, 14 Apr 2015 19:20:34 GMT

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

Re: List a field based on possible values in another column

Tue, 14 Apr 2015 19:22:09 GMT

Hello Anbu,

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

Thanks,
Samira

Re: List a field based on possible values in another column

Tue, 14 Apr 2015 19:25:46 GMT

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

Re: List a field based on possible values in another column

Anonymous — Tue, 14 Apr 2015 19:34:51 GMT

Sure. See the attached.

Re: List a field based on possible values in another column

anbu1984 — Wed, 15 Apr 2015 06:26:11 GMT

Check this app

Re: List a field based on possible values in another column

Wed, 15 Apr 2015 14:18:40 GMT

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