topic Set Aanalysis - Referencing the cuurent selection in QlikView

Set Aanalysis - Referencing the cuurent selection

Sat, 12 Feb 2011 00:57:45 GMT

Within the current selection, I'm trying to count everybody who's spouse is also selected. Here's what I've got:

= Count({$
[Relation Related ID] = {$[Relation ID]}>
}
distinct [Relation ID])

The part in bold {$[Gifts ID]} is what I know is wrong. I want that to represent all of the Relation ID's of the current selection. I know if i were to say [Relation Related ID] = [Relation ID] it would look for rows where the two fields were equal. What I want is rows where the value in [Relation Related ID] can be found anywhere in [Relation ID] of the current selection. Does that make sense?

Thanks very much for any help you can offer.

Set Aanalysis - Referencing the cuurent selection

Miguel_Angel_Baeyens — Sat, 12 Feb 2011 01:04:29 GMT

Hello,

Maybe using P() function in set analysis?

Count({< [Relation Related ID] = P([Relation ID]) >} distinct [Relation ID])

That would read count any distinct value of "Relation ID" where "Relation Related ID" equals to any of the possible values -not excluded- of "Relation ID".

Is that what you are looking for?

Set Aanalysis - Referencing the cuurent selection

Sat, 12 Feb 2011 01:11:12 GMT

Thanks for the quick response Miguel. That's exactly what I'm looking for, the problem is that we're stuck with Qlikview 8.5 and as I understand it the p{} function hasn't been introduced yet.

I'm tempted to give up, but I want to think that it must be possible given that you can easily compare multiple sets with each other.

Set Aanalysis - Referencing the cuurent selection

Miguel_Angel_Baeyens — Sat, 12 Feb 2011 01:22:24 GMT

Ok,

I don't remember if set analysis in v8.5 allows for example the following syntax:

Count({< Field2 = {"$(=chr(39) & Concat(Field3, chr(39) & ',' & chr(39)) & chr(39))"} >} Field1)

Or something like that, since Concat() allows to get a comma separated list of all possible values in Field3. Those chr(39) are the single quote character, required when the values are literals (strings) so the Concat() function in a text box should return

A','B','C

Without the first and last single quotes, that I'm adding in the set analysis modifier.

It may be worth checking.

Set Aanalysis - Referencing the cuurent selection

Miguel_Angel_Baeyens — Sat, 12 Feb 2011 01:22:26 GMT

Ok,

I don't remember if set analysis in v8.5 allows for example the following syntax:

Count({< Field2 = {"$(=chr(39) & Concat(Field3, chr(39) & ',' & chr(39)) & chr(39))"} >} Field1)

A','B','C

Without the first and last single quotes, that I'm adding in the set analysis modifier.

It may be worth checking.

Set Aanalysis - Referencing the cuurent selection

Sat, 12 Feb 2011 02:02:30 GMT

I'll give this a shot and see if I can make it work, it seems like it should. Thanks!

Set Aanalysis - Referencing the cuurent selection

Mon, 14 Feb 2011 22:25:03 GMT

After removing the quotes this did the trick. Thanks again!