Filter

[#1]

Hello all,

I have an issue creating a filter.

Imagine some groups, composed of persons.
I have 2 tables, one is for the groups (group name, activity...), the other is for the persons (name, age, gender...).

In a cross table, I have something like this :

Group Name Gender
----------------------------------
GroupA John Male
GroupA Jenny Female
GroupB Paul Male
GroupC Karen Female

I have a filter on Gender.

Now I want to see all data for Groups where there is a women.

If I filter on Female, I obtain :

Group Name Gender
----------------------------------
GroupA Jenny Female
GroupC Karen Female


What I want is this :

Group Name Gender
----------------------------------
GroupA John Male
GroupA Jenny Female
GroupC Karen Female



I can add an alias table for "Person" in my script, and apply the filter on this second table.
But this becomes complicated when other tables are linked to these ones, for example Female and Male are in another reference table .

Any better idea?

[#2]

Basically what you ask for can be done like this:

- Select 'Female'

- Select the resulting groups

- Deselect 'Female'

That will give you the result you want.

But I understand you would rather have this done when selecting 'Female' and not have to do the other selecting and deselection.

A possible solution:

Why not use an Action (if on QV9) or a a Macro (if on <=QV8.5) to do what I described as steps? You need to create two buttons; one for female and one for male.

When a gender is selected, the Action should activate the resulting groups and then deselect the gender.

[#3]

Thank you for this suggestion Mark.

But my report is for end users, they need to see that the filter is on 'Female', not on groups.

[#4]

Add a specific table containing a group gender field . which contains all groups containing at least one female gender

LOAD distinct group, 'female' as "group gender" resident table where gender = 'female';

Then filter on this new field group gender...

Rgds,

[#5]

Hi Nico,

I've attached an example showing a possible solution, but there is a problem, the user can't filter clicking on the table trough gender field, it must be done trough the FGender field.

I hope this works for you.

Best regards.

Ups, I didn't see the spastor reply, it's right what he wrote.

[#6]

Hello

Thanks everyone for your answers.

I confirm that these solutions can work. This is in some way close to the solution I was proposing, adding an alias table, and apply the filter on it.

[#7 · Accepted solution]

I think you can do it without using a second table. All you should need is Set Analysis and the Concat function.

Concat should give you the list of possible values, so when you select a Gender, you can get the list of groups that contain one member of that gender using Concat. It can get a little tricky with setting up the quotes correctly, but I was able to get it working using your data.

I set up a chart with Group and Name as the dimensions. Then I used this for the expression:

Only({1<Group = {"$(=Concat(Group, '","'))"}>} Gender)

[#8]

Great! This is what I was looking for.

Question : What is the limit (maximum length) of the string returned by Concat()?

[#9]

I'm not sure about a limit. There is nothing in the Reference Manual.

I did a quick test and was able to get over 2,000 characters from the concat. I suppose there could be a limit, but it seems to be high at least.

If you're looking at the max number of values it can pull, I'm not sure on that either. I've never heard anyone complain about running into a limit, so again I would assume if there is one it is large.