Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
SchemeNo={
$(
=concat(DISTINCT{<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_First)"}>}PeerSchemeNo)>
*
<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_Last)"}>}PeerSchemeNo)>}PeerSchemeNo,', ')
)
}
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)
Try it with:
count(DISTINCT
{1
<[PeerGroupName]=P({<PeerGroupName={"$(vPG1)"}>}PeerSchemeNo)>
*
<[PeerGroupName]=P({<PeerGroupName={"$(vPG2)"}>}PeerSchemeNo)>
}
SchemeNo
)
- Marcus
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
SchemeNo={
$(
=concat(DISTINCT{<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_First)"}>}PeerSchemeNo)>
*
<[PeerSchemeNo]=P({<PeerGroupName={"$(vPG_Last)"}>}PeerSchemeNo)>}PeerSchemeNo,', ')
)
}