Subtract Query

[khansen97]

I am trying to create a subtract query but it is not working. What I want is a list of IDs that are not in query 2 or query 3.

In BO terms this is what I am trying to do:

Query 1:
Family; ID; Sales

Subtract 2:
Family; ID; Orders - Where Program in list (23,35,54)

Subtract 3:
Family; ID; Retail Order - Where Program in list (57,54,40)

It doesn't seem to be working. I keep getting IDs returned that show up in Query 2 and three. I tried to use the same measure for all three, but the contexts are not the same so I am trying to set the context by changing the measures.

Any ideas?

 

[skrandel]

You can't have measures in the query. MINUS queries only make sense without the numbers.

A minus query doesn't just subtract the dimensions, it only works if the entire row matches.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Are you working with version 6?
I can think off using the added functionality of using query on query to solve this one..

Other ideas:

Store the ID's that are to excluded first in a database view (with union to store the ID's from the 2 tables) and use an object from the view in a subquery in query panel.

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Why not use the not in list function and set up queries 2 and 3 as sub-queries...

The process would be to set a report to return query 1 then in the conditions:

ID Not in list (use subquery, simply use ID as your return from the subquery and set the conditions as required)

then add another condition ID not in list (now set up a secound subquery with the conditions you have set out above, again using ID as the returned value)

I can give you more info if possible, I currently have queries with up to 9 subqueries including some subqueries of subqueries..