UNIOn Query to Find Records w/ Maximum

[Cognos_Jan2017]

Have UNION Query, but want to find records where ...
[Date of Incident]=Maximum([Date of Incident])
The 2 Queries in the UNION can have common Locations
w/ different [Date of Incident]

Trying that only brings in one record.

Trying to add the UNION Query to a new Query isn't allowed.
IF it was, wanted to run ...
[Date of Incident]=Maximum([Date of Incident])

TIA, Bob

[MFGF]

Have UNION Query, but want to find records where ...
[Date of Incident]=Maximum([Date of Incident])
The 2 Queries in the UNION can have common Locations
w/ different [Date of Incident]

Trying that only brings in one record.

Trying to add the UNION Query to a new Query isn't allowed.
IF it was, wanted to run ...
[Date of Incident]=Maximum([Date of Incident])

TIA, Bob

Hi,

I'd expect a filter defined as [Date of Incident]=Maximum([Date of Incident]) would only bring in rows for one date from the entire data set. Have you tried baking the Location into this expression, eg

[Date of Incident]=Maximum([Date of Incident] for [Location])

Then you'd get rows for the locations with the latest date in each. Is this what you need?

Why do you think adding the UNION query to another query isn't allowed? You can union/join etc your query just like you can with any other query. What issues do you see?

Cheers!

MF.

[Cognos_Jan2017]

[Date of Incident]=Maximum([Date of Incident] for [Location])

Then you'd get rows for the locations with the latest date in each. Is this what you need?

Why do you think adding the UNION query to another query isn't allowed? You can union/join etc your query just like you can with any other query. What issues do you see?

Thank you MFGF.

Summary filter of ... [Date of Incident]=Maximum([Date of Incident] for [Location]) WORKS !!!

When trying to add the UNION query to a new query, i see ...
Query items from one query can't be added to another query.

But now, I don't need that additional query.

You are A-OK MFGF.  THANKS again, Bob

[MFGF]

Thank you MFGF.

Summary filter of ... [Date of Incident]=Maximum([Date of Incident] for [Location]) WORKS !!!

When trying to add the UNION query to a new query, i see ...
Query items from one query can't be added to another query.

But now, I don't need that additional query.

You are A-OK MFGF.  THANKS again, Bob

Hi,

You can add items from one query to another query if they are linked somehow in the query explorer. If they are disconnected, you can't by default (unless you enable cross-product joins). We just need to clear that up, because someone searching in the future when trying to learn Cognos might find this post and reach the conclusion you can't add query items from one query to another, when actually that's not the case at all. :)

Do you understand why your original expression didn't work and the new one does? Your original expression [Date of Incident]=Maximum([Date of Incident]) has no context for the Maximum specified, so in the absence of all else it will compare the date in each row with the maximum date from your data set, and discard those rows that don't match. In your case it left you with only one row, as that was the only one in the data set with that maximum date value in it. Changing the expression to [Date of Incident]=Maximum([Date of Incident] for [Location]) now introduces a context for the Maximum specified, and thus rather than getting a single overall max date to compare the rows against, the data set is grouped by Location and the maximum for each group is determined. The filter then omits rows from the group that don't match. In your case it leaves you with one row per Location - the row with the maximum date for that location.

I'll take "A-OK" any day of the week! High praise indeed! :)

MF.

[Cognos_Jan2017]

Do you understand why your original expression didn't work and the new one does? Your original expression [Date of Incident]=Maximum([Date of Incident]) has no context for the Maximum specified, so in the absence of all else it will compare the date in each row with the maximum date from your data set, and discard those rows that don't match. In your case it left you with only one row, as that was the only one in the data set with that maximum date value in it. Changing the expression to [Date of Incident]=Maximum([Date of Incident] for [Location]) now introduces a context for the Maximum specified, and thus rather than getting a single overall max date to compare the rows against, the data set is grouped by Location and the maximum for each group is determined. The filter then omits rows from the group that don't match. In your case it leaves you with one row per Location - the row with the maximum date for that location.

Yes, I fully understand needing ...
[Date of Incident]=Maximum([Date of Incident] for [Location])

Your explanation above is a great description to serve as a standard getting that done correctly.
A-OK again !!!  THANK you.

You can add items from one query to another query if they are linked somehow in the query explorer. If they are disconnected, you can't by default (unless you enable cross-product joins). We just need to clear that up, because someone searching in the future when trying to learn Cognos might find this post and reach the conclusion you can't add query items from one query to another, when actually that's not the case at all. :)

Yes, when I tried that, it didn't work.  I had no joins so maybe that is why it didn't work. I have programmed Microsoft Access queries many years and could always bring Fields from other queries into a new query.

Still need to do a Data Module w/ Packages as we discussed.  Should today or tomorrow and post back in the Topic.

Thanks again, Bob

[Cognos_Jan2017]

You can add items from one query to another query if they are linked somehow in the query explorer. If they are disconnected, you can't by default (unless you enable cross-product joins). We just need to clear that up, because someone searching in the future when trying to learn Cognos might find this post and reach the conclusion you can't add query items from one query to another, when actually that's not the case at all.

Back to this.  I am writing another Report where I have run a query with Query Items (as filters)
to ultimately utilize just 4 Query Items of those Query Items for an EXCEPT query.  There will be
2 queries each of 4 Query Items for matching.

Am getting the "Query items from one query can't be added to another query." again.

How do I be sure that "they are linked somehow in the query explorer." ?

Feel like a dumb question but I say to others if someone doesn't know an answer (but wants to find out),
that NO question is a dumb question.

TIA, Bob

[Cognos_Jan2017]

My Post directly above ...
I run 10 Query Items to be able to ultimately utilize 4 of them for an EXCEPT query.

I could use all 10 of those Query Items (and "match" them to another query
w/ 10 Query Items), and drag in needed Data Items to a List.  Is that the
"best way", or can 4 those 10 Query Items be added to a new query?

TIA, Bob

[Cognos_Jan2017]

My Post directly above ...
I run 10 Query Items to be able to ultimately utilize 4 of them for an EXCEPT query.

I could use all 10 of those Query Items (and "match" them to another query
w/ 10 Query Items), and drag in needed Data Items to a List.  Is that the
"best way", or can 4 those 10 Query Items be added to a new query?

More on this ...
The first query uses 10 Data Items to find "Severity of an Injury".

The second query uses 6 Data Items to find the Category of "an Injury".

From those 2 queries, we need to look at only 4 Data Items ...
1 - Business Group
2 - Incident ID
3 - Business Location
4 - Fiscal Year

I had run 2 different Reports, dragging in the 4 Data Items from queries for "Severity" and "Injury".

With the above, I saved 2 Excel files and made them part of a Data Module. Within that Data Module,
we add an EXCEPT query to find "Injuries" that DON'T have a corresponding "Severity" entry.  This lets
Users know where a "Severity" record MUST be entered for an "Injury".

That WORKS, but the negative is having to run those Cognos Reports,
saving as Excel files, and uploading back into the Data Module.

From my Data Module recent thread, I understand why do all that when the
SQL Server Package is available and automatically updated.

Bottom line ... right now, when trying to drag in 4 Data Items from the "Severity" and "Injury" queries, we see ...
"Query items from one query can't be added to another query."

How can we correct that?

TIA, Bob

[MFGF]

Back to this.  I am writing another Report where I have run a query with Query Items (as filters)
to ultimately utilize just 4 Query Items of those Query Items for an EXCEPT query.  There will be
2 queries each of 4 Query Items for matching.

Am getting the "Query items from one query can't be added to another query." again.

How do I be sure that "they are linked somehow in the query explorer." ?

Feel like a dumb question but I say to others if someone doesn't know an answer (but wants to find out),
that NO question is a dumb question.

TIA, Bob

Hi,

For a Union, Intersect or Except, you need to bring in the relevant toolbox object in the Query Explorer. The image below is from Cognos 10, but it's the exact same concept in CA too



You need the same number of items in each query (and of the same overall data types) to be able to union/intersect/except

Cheers!

MF.

[Cognos_Jan2017]

Yes, I understand the requirements for a UNION, but
how do I take the 2 queries of ...
1 - 10 Data Items
2 - 6 Data Items
... to select 4 common Data Items from each query above, to UNION, when I see ...
"Query items from one query can't be added to another query."

TIA, Bob

[MFGF]

Yes, I understand the requirements for a UNION, but
how do I take the 2 queries of ...
1 - 10 Data Items
2 - 6 Data Items
... to select 4 common Data Items from each query above, to UNION, when I see ...
"Query items from one query can't be added to another query."

TIA, Bob

Arrange your queries in the Query Explorer as below:



10ItemQuery has your 10 items in it. This feeds into 4From10Query, which contains only the four items from 10ItemQuery you want to union
6ItemQuery has your 6 items in it. This feeds into 4From6Query, which contains only the four items from 6ItemQuery you want to union

You then union 4From10Query and 4From6Query into the final query for your reporting container

Cheers!

MF.

[Michael75]

You then union 4From10Query and 4From6Query into the final query for your reporting container

Another way to handle this would be to include dummy items in your "6ItemQuery", respecting of course the order and type of those in your "10ItemQuery", so as to align the layout of the two queries. Then you don't need the "n from n" queries.

So the first missing column, if numeric, would be Dummy1, value 0. and the second, if alphanumeric, would be Dummy2, value ' '. Etc. etc.

I don't know which of these two suggested solutions would be better from the POV of the generated SQL.

[Cognos_Jan2017]

THANK you both MFGF and Michael75.

Just getting up today after studying from 3-to-6 am today.  Will learn from both of your recommendations.

My project plans to do lots of queries like these, including mixing Excel files w/ Package data in Data Modules.

Will test these at work, and report back.  Thanks again, Bob

[Cognos_Jan2017]

MFGF and Michael75 ... I am having a problem opening the Package brought into the Data Module.

For the queries I had in the Data Module, I had copied and pasted Data Items from the Package "I knew"
(from a non-Data Module) was our Company Folder Source (the App has many Data Module Package
Paths to other Companies, and I don't see our Company Path).

The data I see from copy-and-paste existing queries appears to be part of a "Link" to our App.

Trying to add the 4 Data Items needed from the "Package" provided by the App appears NOT to
be the same syntax as the Data Item Names in "our known Package".

Trying to write new queries from the "Package" provided in making a new Data Module appears
to NOT be our Company data.

I will contact our App Vendor to see how I can ensure the data I am seeing is correct.  That means
a delay until next week until testing this properly ... My Boss is out-of-State, and I need to review
this w/ him as he is the Contact w/ the App's Service Rep.

Will reply in this thread w/ this is resolved.

THANK you both, and have a good weekend ... Bob

[Cognos_Jan2017]

MFGF and Michael75 - I received a reply from our App's Vendor ...

Hi Bob, as mentioned during User conference 2017 and our monthly meetings, the current packages will not completely support building data modules. We are working on dynamic packages that can be used with Data modules and you would hear more updates during monthly user group meetings.

This means our current use of Data Modules will be unable to include Packages.

The previous method we used will continue to work, but requires maintenance.

We will be fine.

I am not sure that, given the current restriction, we won't be able to add "other" dummy Fields
to have matching number of Fields for a UNION query.

THANK you both for showing me methods of accomplishing this, Bob

[MFGF]

MFGF and Michael75 - I received a reply from our App's Vendor ...

This means our current use of Data Modules will be unable to include Packages.

The previous method we used will continue to work, but requires maintenance.

We will be fine.

I am not sure that, given the current restriction, we won't be able to add "other" dummy Fields
to have matching number of Fields for a UNION query.

THANK you both for showing me methods of accomplishing this, Bob

Assuming your package is based on relational tables in a database, couldn't you just include those tables in your data module as an alternative to using the package?

MF.

[Cognos_Jan2017]

The Package IS relational data.

As for including the Tables ... the two Excel files
(carefully matched for a UNION) have actual data.

To ensure always seeing actual data, don't I need to
have that data from the Package ... like it is when I make the
Excel files from Cognos Reports?

[MFGF]

The Package IS relational data.

No it isn't. It's an XML definition that describes relational data. It is metadata - ie it isn't the data itself. The relational data the package describes lives in tables in the database. I was suggesting that if the package doesn't work for you in your data module, maybe linking directly to the underlying tables will?

As for including the Tables ... the two Excel files
(carefully matched for a UNION) have actual data.

To ensure always seeing actual data, don't I need to
have that data from the Package ... like it is when I make the
Excel files from Cognos Reports?

As above, the package doesn't contain data. It simply describes the data that lives in tables in the database, and presents it in (hopefully) intuitive form. When you use a query item from a package, Cognos resolves this to a column in a database and sends a SQL query to the database to retrieve it.

If the package isn't working for you in this case, you could perhaps point your data module at the database tables the package describes?

Cheers!

MF.

[Cognos_Jan2017]

No it isn't. It's an XML definition that describes relational data. It is metadata - ie it isn't the data itself. The relational data the package describes lives in tables in the database. I was suggesting that if the package doesn't work for you in your data module, maybe linking directly to the underlying tables will?

I am not sure the App Vendor will let us link to those SQL Server Tables?
I have no "Control" over the App's Framework Manager, and thus its Packages.

What do I ask the Vendor, specifically, on how to "Link" to those Tables so I can "feed"
the Query design you kindly sent last week?  The Vendor said our Package/ Data is
NOT available to utilize in a Data Module.

IF we try the required UNION, I guess we could get those Data Items from a direct Link?
Then write the needed queries of 4 Data Items each, and UNION those.

Listening to learn.  Thank you MFGF, Bob

[Cognos_Jan2017]

I have listed this in the App Vendor's Cognos discussion group ...  no reply yet.

In a Cognoise discussion, it was suggested ... Bob again --> Is there a way to point my Data Module at the database tables the package describes? If so, we might be able to write a UNION query of 4 Fields, fed by 2 other queries of 10, and 6, Fields respectively ... BELOW is Cognoise suggestion. TIA, Bob
As above, the package doesn't contain data. It simply describes the data that lives in tables in the database, and presents it in (hopefully) intuitive form. When you use a query item from a package, Cognos resolves this to a column in a database and sends a SQL query to the database to retrieve it.

If the package isn't working for you in this case, you could perhaps point your data module at the database tables the package describes?