topic Re: Set analysis intersection in QlikView

Set analysis intersection

juleshartley — Sat, 16 Nov 2024 02:30:52 GMT

HI All,

I'm trying to set the selections in one field 'SchemeNo' to the values of 'PeerSchemeNo' that are the intersection of 2 simple sets.

I've tried this, but it seems to only return 0 (there is definitely some overlap)

count(DISTINCT

{1
<[SchemeNo]=P({<PeerGroupName={"$(vPG1)"}>}PeerSchemeNo)>
*
<[SchemeNo]=P({<PeerGroupName={"$(vPG2)"}>}PeerSchemeNo)>
}
SchemeNo
)

If I set it all to use 'PeerSchemeNo' instead of SchemeNo, then I get the correct number of intersecting schemes. But it is not that field that I want to make affect. Also, if I just use one of the 'P' sets above then it does give me the expected answer, so they seem to be working in isolation.

Thanks,

Julian

Re: Set analysis intersection

sunny_talwar — Wed, 11 Sep 2019 11:40:23 GMT

Without a sample, this is more going to be a guess work, but try this

Count(DISTINCT {1<[SchemeNo] = P({<PeerGroupName = {"$(vPG1)"}>} PeerSchemeNo) * P({<PeerGroupName = {"$(vPG2)"}>} PeerSchemeNo)>} SchemeNo)

Re: Set analysis intersection

marcus_sommer — Wed, 11 Sep 2019 12:00:52 GMT

Try it with:

count(DISTINCT

{1
<[PeerGroupName]=P({<PeerGroupName={"$(vPG1)"}>}PeerSchemeNo)>
*
<[PeerGroupName]=P({<PeerGroupName={"$(vPG2)"}>}PeerSchemeNo)>
}
SchemeNo
)

- Marcus

Re: Set analysis intersection

juleshartley — Wed, 11 Sep 2019 15:20:42 GMT

Thanks both - I'm not sure why it didn't work but I've ended up using a concat to build a string to feed into the set analysis, which seems to work ok

Re: Set analysis intersection

juleshartley — Wed, 11 Sep 2019 15:21:48 GMT

SchemeNo={

=concat(DISTINCT{<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_First)"}>}PeerSchemeNo)>

<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_Last)"}>}PeerSchemeNo)>}PeerSchemeNo,', ')

)

}