set Analysis - returning a list of Names by Department

[#1]

Hi,

I am trying to use set analysis to return only a list of Names against certain departments.

I have used set analysis before to return a numeric value but I dont now how to return a Field conataining Names

TOTAL({<Department={'Football', 'GAA'}>} NAME)

I dont know what function to use at the start to return a string of names, before I used sum / num TOTAL when I was returning numberic values.

Thanks

Gregg

[Miguel_Angel_Baeyens]

Hi Gregg,

I'd use the Concat() function, that allows set analysis as well. That will return a string of values comma separated (by defaul, you can change it).

Concat({< Department = {'Football', 'GAA'} >} DISTINCT NAME, ',')

Hope that helps.

Miguel

[#3]

Hi Miguel,

I am trying to display the result in a list box. Do you know if this is possible?

What we require is only a list of names to be returned against certain departments.

At the moment, the list box populates all employees.

Thanks,

Gregg

[jagan]

Hi,

Try this expression

=Aggr(If(Match(Department, 'Football', 'GAA'),  Name, NULL()), Department)

Regards,

Jagan.

[Miguel_Angel_Baeyens]

Hi Gregg,

The Only() function should work with the same set analysis in a listbox as expression:

Aggr(Only({< Department = {'Football', 'GAA'} >} NAME), NAME)

Hope that helps.

Miguel

[#6]

HI Jagan,

When I enter the expression in the list box, (displaying the Name field)

It now returns all the employees and a ‘ – ‘ beside it,

Joe Bloggs -

Derek -

Paul -

Thanks,

Gregg

[#7]

Hi Miguel,

Still no joy

Regards,

Gregg

[Miguel_Angel_Baeyens]

Hi,

What version of QlikView are you using? That's working in my end.

Script:

Data:
LOAD * INLINE [
Department, Name
A, Albert
A, Alfred
B, Bertrand
B, Bill
C, Charlie
C, Chuck
];

Function:

=Aggr(Only({< Department = {'A', 'B'} >} Name), Name)

Hope that helps.

Miguel

[#9]

Hi,

Version 11

Regards,

Gregg

[Miguel_Angel_Baeyens]

Gregg,

Check the updated post.

Miguel