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

 

Issue Joining Text Field from Excel to Text Field from Data Model

Started by pacificbeavs, 05 Jun 2019 09:56:56 AM

Previous topic - Next topic

pacificbeavs

Cognos 11.1 - I have an Excel document that I uploaded with a bunch of IDs stored as text.  I'm trying to join those to the IDs (also stored as text) in a data model within Cognos in order to create a data module with the two sources combined.  However, it won't let me make that join.  Do you know why this is, or how to resolve it?

Thanks!

bus_pass_man

Could you be a bit more specific about "it won't let me make that join" please.

Are you getting an error?   Are the query items which you want to use as keys showing up in the relationship editor?  If you test the relationship do you get data back (i.e. are there matching values on either side of the join)?  Are you sure the data types are compatible?


pacificbeavs

Quote from: bus_pass_man on 05 Jun 2019 01:07:14 PM
Could you be a bit more specific about "it won't let me make that join" please.

Are you getting an error?   Are the query items which you want to use as keys showing up in the relationship editor?  If you test the relationship do you get data back (i.e. are there matching values on either side of the join)?  Are you sure the data types are compatible?

Sorry for the late reply.  In the "Create Relationship" screen, I am able to see the ID to the left and to the right of the respective tables.  However, down below the model I'm trying to join to says "You can't preview this data", and when I click further it mentions cross joins not permitted.  I should mention that this model works fine outside of trying to create this data module.  When I select the ID from each table and hit "Match selected columns" it just shows a red circle with a line through it, but no further explanation as to why it won't make the join.  The data types are both text.

bus_pass_man

I'm still a wee bit confused about what you are seeing, so I hope to see if I understand the situation and get you further down the road to solving the puzzle.

You have a module where there's a bunch of tables ( or at least one).  Is that a correct understanding or is it a bit more complicated such as being a FM package or a data set or something?

You've uploaded a xls to your CA server.  You've added it to the module.   You've selected the xls and a table in the module and choose create relationship from the context menu.  Is that a correct understanding?

Just to confirm, when you select the query items you want to use as keys, the data grids to the sides of the dialog return data.  Is that a correct understanding?

QuoteI should mention that this model works fine outside of trying to create this data module.
This puzzles me.  Is this a literal statement or just misspeaking? Are the objects in different modules and you're trying to join them in a third module?  What is the "model" which mention here (and a couple of other places)?  Is it a FM package?

QuoteHowever, down below the model I'm trying to join to says "You can't preview this data", and when I click further it mentions cross joins not permitted.

Just out of curiosity, what is the objective of the exercise?  That might help me figure out what's going on.  If I was in your cubicle, I could see what is on your screen and what you're pointing at but I'm not.

Are you trying to create aliases? That would explain the cross-join error; there was no way to do aliases until 11.1.1, when copy and paste and views/joined views/etc. were introduced.

Also, what release are you using.

pacificbeavs

Quote from: bus_pass_man on 12 Jun 2019 06:18:58 PM
I'm still a wee bit confused about what you are seeing, so I hope to see if I understand the situation and get you further down the road to solving the puzzle.

You have a module where there's a bunch of tables ( or at least one).  Is that a correct understanding or is it a bit more complicated such as being a FM package or a data set or something?

You've uploaded a xls to your CA server.  You've added it to the module.   You've selected the xls and a table in the module and choose create relationship from the context menu.  Is that a correct understanding?

Just to confirm, when you select the query items you want to use as keys, the data grids to the sides of the dialog return data.  Is that a correct understanding?
This puzzles me.  Is this a literal statement or just misspeaking? Are the objects in different modules and you're trying to join them in a third module?  What is the "model" which mention here (and a couple of other places)?  Is it a FM package?

Just out of curiosity, what is the objective of the exercise?  That might help me figure out what's going on.  If I was in your cubicle, I could see what is on your screen and what you're pointing at but I'm not.

Are you trying to create aliases? That would explain the cross-join error; there was no way to do aliases until 11.1.1, when copy and paste and views/joined views/etc. were introduced.

Also, what release are you using.

I need to set some alerts so I get emails on this topic =).  Sorry I wasn't more specific, it's an Excel file and a FM package, and your initial summary is correct.  And yes, the data grids to the side return values.

I'm using 11.1 R1.  I have a spreadsheet of Claim IDs (think health insurance) and some additional information about those claims contained within that spreadsheet, and I'd like to join on Claim ID to those same claims within the FM package.  Once this data module is complete then I can create a report that contains info from the FM package along with the information from the spreadsheet.

First lines of Error:

XQE-PLN-0091 Cross joins (between query subjects: [Business Layer].[Claim Fact], [Data Layer].[PROVIDER_DIM]) are not permitted.
   at com.cognos.xqe.transformation.v5tocogsql.util.joinRefinement.RQPJoinPathFinder.checkCrossProdAllowed(RQPJoinPathFinder.java:617)
   at com.cognos.xqe.transformation.v5tocogsql.util.joinRefinement.RQPJoinPathFinder.checkGovernorSettings(RQPJoinPathFinder.java:583)