If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Union is dropping data from 1 query

Started by bcksr, 23 Jan 2018 11:07:46 PM

Previous topic - Next topic

bcksr

Hello - I am creating a union on two queries that are each created by a join. When I run each query itself my data is accurate. When I create the union, I get the correct total count but it is dropping data from one of the queries. All columns are the same in each? I'm very new to Cognos and am quite stumped on this.

EX:
Q1 join Q2 to create Q3

Q4 join Q3 to create Q5

Q6 join Q3 to create Q7

Union Q7 & Q5 to create final Q

hope that makes sense... when I run Final Q - I am dropping specifically the data from Q3 that is in Q7. But when running Q7 independently it is correct.


Lynn

Quote from: bcksr on 23 Jan 2018 11:07:46 PM
Hello - I am creating a union on two queries that are each created by a join. When I run each query itself my data is accurate. When I create the union, I get the correct total count but it is dropping data from one of the queries. All columns are the same in each? I'm very new to Cognos and am quite stumped on this.

EX:
Q1 join Q2 to create Q3

Q4 join Q3 to create Q5

Q6 join Q3 to create Q7

Union Q7 & Q5 to create final Q

hope that makes sense... when I run Final Q - I am dropping specifically the data from Q3 that is in Q7. But when running Q7 independently it is correct.

There is a property called "Duplicates" that allows you to select Remove or Preserve. Could this be causing you the problem? If you know SQL it is the difference between UNION and UNION ALL. If there is a row in the Q7 result set that is identical to a row in the Q5 result set then this setting will determine whether both rows are returned in the final result set or not.

Francis aka khayman

also check your query property... Auto Group & Summarize... set accordingly