Selecting Possible Values?

[#1]

Hi Guys

Please have a look at the attachment . I have a situation where I want to select the possible values of Host ,for the selections made in other fields. And i want to see all the data corresponding to these hosts.I have created two tables for current and desired output . Can you please explain how i can achieve this.Please reply asap.

[sunny_talwar · Accepted solution]

Check this expression may be:

=Count(DISTINCT {1<Host = p(Host)>} Host)

[swuehl]

You could also use the p() function in a set expression with default set identifier and explicitely clearing selections in other fields, like

=Count(DISTINCT {<Host = p(),Division=>} Host)

(Maybe this has no benefit in this exact scenario, but if you need to consider some user selections in your aggregation)

[sunny_talwar]

Stefan -

Is there a difference between Host = p() and Host = p(Host). I think I am seeing this twice in 2 days from you and I was wondering if there is a difference or if they would give the same result always.

Thanks,

Sunny

[swuehl]

I don't think there is a difference between the two. If you leave out the (optional) field name in the p() function, I believe Qlik will use the same field used in the field modifier on the left side of the equal sign to retrieve the possible values from.

This is also what the HELP says (or how interprete the following):

You could use another field name, but this only makes sense if there are matching field values.

My suggestion was more about not using set identifier 1 and instead using a list of fields to clear selections in.

[#6]

This is exactly what i wanted. I knew it could be done using P function , but i haven't used it before and wasn't sure of its usage. Thanks for a quick reply