A "Union" from a Data Module

[Cognos_Jan2017]

Our 1st Data Module ... Have Changed Label Names. The 4 leftmost Columns are from Cloud App data, while the 3 Columns on the far right are from Excel data prior to getting the Cloud App.

No problem Creating a Data Module, but HOW can we take the data from the 3 Columns on the far right, and "Union" them to the 4 Columns on the far left? Here, the row of "Downtown" is what is needed (No "CaseNumber' in the pre-ProcessMAP Excel file).

Hope the Excel attachment works.

TIA, Bob   

[MFGF]

Our 1st Data Module ... Have Changed Label Names. The 4 leftmost Columns are from Cloud App data, while the 3 Columns on the far right are from Excel data prior to getting the Cloud App.

No problem Creating a Data Module, but HOW can we take the data from the 3 Columns on the far right, and "Union" them to the 4 Columns on the far left? Here, the row of "Downtown" is what is needed (No "CaseNumber' in the pre-ProcessMAP Excel file).

Hope the Excel attachment works.

TIA, Bob

Hi,

Data modules don't support union/except/intersect options. To union these you'd need to create a report with two queries - each fed from the two files in the data module respectively - and union the queries in the report.

You also have the problem that a union requires a contiguous result set. You can't take a set of four columns and union that with a set of three columns - you'd need four columns on each side of the union. You'll need to create a query calculation in your report to emulate the missing column before the union can be performed.

Cheers!

MF.

[Cognos_Jan2017]

I thought that would be the solution.

Thank you MFGF, Bob

[Cognos_Jan2017]

Having a problem w/ a UNION Query ... IF I am doing this correctly?

When Creating the Data Module, I Uploaded both Excel files, for simplicity
named "NewData" and "OldData".  Both files are in the Data Module w/ NO
Join.  From my Access programming days (and I don't think this applies here),
an Access Query w/ 2 unjoined Tables results in a Cartesian Product.

When making a List Report, I use the newly created Data Module as the Source,
and do see the 2 Excel files (now each w/ 4 Columns w/ matching Column Names).

I View tabular data, separately, for "NewData" and "OldData".  ALL data is displayed.

I drag in a new "Query1" and drag a Join to the right of that.  I then drag (into the
2 available Join objects) ... "NewData" then "OldData".

Running "View tabular data" for the Join Query, "Query1", results in nothing displayed.
Any idea why Nothing is displayed?

TIA, Bob

[Cognos_Jan2017]

When setting "Query1" as the Data Query for the List results in the Error ...
XQE-PLN-0094 The query 'Query1' references the non-existent query 'Q_Old_Recordables'.
RSV-SRV-0042 Trace back:

NOTE:  The query 'Q_Old_Recordables' is from "OldData".

Again, View tabular data for each of the new Queries (from "NewData" and "OldData"
files), in the UNION, displays ALL data.

TIA, Bob

[MFGF]

Having a problem w/ a UNION Query ... IF I am doing this correctly?

When Creating the Data Module, I Uploaded both Excel files, for simplicity
named "NewData" and "OldData".  Both files are in the Data Module w/ NO
Join.  From my Access programming days (and I don't think this applies here),
an Access Query w/ 2 unjoined Tables results in a Cartesian Product.

When making a List Report, I use the newly created Data Module as the Source,
and do see the 2 Excel files (now each w/ 4 Columns w/ matching Column Names).

I View tabular data, separately, for "NewData" and "OldData".  ALL data is displayed.

I drag in a new "Query1" and drag a Join to the right of that.  I then drag (into the
2 available Join objects) ... "NewData" then "OldData".

Running "View tabular data" for the Join Query, "Query1", results in nothing displayed.
Any idea why Nothing is displayed?

TIA, Bob

Hi,

Can you explain why you're using a Join object in your query explorer? The idea is to union the queries, not join them?

Have you tried bringing in a Union object?

Here's an example from Cognos 10 Report Studio, but the concept is exactly the same in Cognos Analytics Report Authoring. You need a Union object just like in the image.



Cheers!

MF.

[Cognos_Jan2017]

Thank you, MFGF.  The "Join" was my error.

After posting yesterday, I found this in a 2016 Google Search, where you had contributed ...
http://www.cognoise.com/index.php?topic=30066.0

I wound up adding a 2nd Union in addition to the first, as View tabular data for "Query1" results in nothing
like the cognoise 2016 Topic URL above.

Attaching file to show current working "Query2"s "Union2..

Please let me know if I haven't given a good explanation above.  Thank you, Bob