BusinessObjects Board

Counting an Intersection of 2 lists of ID's from different Data Providers

I have 2 lists that come from different data providers. I’m trying to count when ID’s from ID object 1 appear also in ID object 2.

I know about combined queries, and I wish this issue were that simple, but unfortunately the data comes from 2 different universes and for the purposes of this post, solutions involving universe design or combined queries is off the table.

Here’s the rub: I actually can get it to provide me with an intersected list. Here’s how:

  1. Merge ID1 and ID2 into Merged ID
  2. create variable (varID1) of ID1 and set it to be a detail of Merged ID
  3. create variable (varID2) of ID2 and set it to be a detail of Merged ID
  4. create intersection variable (varIntersection): =if(varID1=varID2) then varID1
  5. The list appears to be working appropriately, and using the count function yields an appropriate count in the table block
  6. Create a count variable (varCountIntersection): =count(varIntersection)
    This results in a count which does not match the count in the block.

I’ve tried a variation using the match function and converting the numeric id’s into text, but this too yielded counts that were way off. Something is happening when I take the function out of being in the context of a table, which is super frustrating because nothing is being changed, there is no need to change contexts, I need it to count them just like how it would in the table.

I’ve seen a lot of related posts on this but nothing quite exactly helping with the counting issue I’m having. Any help is much appreciated.

  • which ID-field did you insert into your report ? left-, right- or inner-join ?
  • give us a simple example of your data and the expected result, so we can better understand the report-context and your problem

Can you please explain what you mean by left/right/inner join? If you have a link that would be fine also, I have heard these terms used but I’m not really sure what that means at all. #5 is in reference to the variable varIntersection: =if(varID1=varID2) then varID1 - so in the block a subset of ID1 is shown and displays properly. The issue is that when a count object is created, it does not behave outside of the table how it does inside.

As this is proprietary information, it is inappropriate for me to share company data on an open forum site. I have two ID’s from two universes but they reference the same thing, so the ID’s from universe 1 and universe 2 are identical in terms of objects and there is no issue in the actual merging of the ID’s.

you can use the merged “ID” in your report (acts as inner-join)
or “Query1.ID” (left-join)
or “Query2.ID” (right-join)
https://answers.sap.com/questions/6404997/joins-inner-join-right-left-joins-with-merge-dimen.html

also pay attention to the context when calculating values inside or outside of a report/block/…
https://blogs.sap.com/2014/12/19/all-you-need-to-know-about-in-foreach-forall-context-operators/

1 Like

Thanks for the link, although I’m afraid I’m no closer to understanding what a join is or means, it isn’t explained what a join is in the article only how it works in WEBI. I can’t seem to find anything at all explaining what inner, right, and left joins are.

In regards to contexts, I have read that article many times thank you for providing it though. It does not help explain why the previously mentioned 6. “(Create a count variable (varCountIntersection): =count(varIntersection) This results in a count which does not match the count in the block)” happens. I cannot refer to “block” when I’m trying to aggregate without the ID’s present. There is no other context than the ID’s being used. Using IN, foreach, and forall for the id’s yields no results.

Intersections are incredibly difficult to achieve in WEBI and I am not finding any documentation on how to achieve intersections at the report level other than by doing so in a table but this does not meet the requirements, which are not ID specific but must be aggregated without the specific ID’s present to force it to behave.

I found explanations of joins - no need to respond further on that point thanks for the link!

The issue I’m experiencing is that merging dimensions creates a union, the SAP article you provided says it behaves as an inner join. That’s not true, it behaves as a union, and counting those id’s will result in a union. That’s the entire issue I’m having, there’s no straightforward way to have an inner join at the report level.

try with a small subset of your data or another small report to learn how formulars in WebI are working and what you get when merging data.
also use the countless Youtube- and Google-possibilities to close your knowledge gaps
and/or ask your Database or Basis-colleagues for helping you with pre-calculating the data-source, if it is not possible in WebI.