How to fetch Query A minus Query B in Webi Report from 2 different Data Providers

[former_member702336]

Hi All,

Can anyone tell me how to implement Query A Minus Query B in Webi.

Query A is based on custom SQL having 5 columns.

Query B is based on an excel source having 5 columns.

Requirement is to check every row from Query A and if that is not available in Query B, will have to be displayed in the result set.

Combined queries/minus operator cannot be implemented as these data providers are not based on the universe.

Thanks

[former_member654264]

Let me know if you found something. I am having the same issue. And I can't pass the IDs to another query because I have almost 2k IDs so I exceed the 1K limit.

[nscheaffer]

You need to have an ID value that connects your queries (e.g. Customer Number, Order ID, etc.).

Basically we are going simulate a left join; all the data from Query A that doesn't match row in Query B.

Here are my sample queries...

After running your queries merge on that common ID value. In my example I am merging the State objects.

Next, create a variable equal to any object from Query B other than what you just merged and set its Qualification to "Detail" with "State" as the Associated Dimension.

Create a filter on your table containing data from Query A where the variable just create is null.

And there you have it.

You could use a similar approach to do...

B - A

A or B, but not both

A and B

There is a lot more to merging that what I have described here, but hopefully this will get you going down the right path giving you some ideas to try and concepts to explore.

Noel