Self-Joining Table

[roddawg]

So I am modeling a third party vendors data.  In their structure they have created a catch all table that stores 1 pages elements by referencing Screen=? Field=? Value=?  The table structure looks like:

District	EMPID	Screen	Field	Value	Change Date	ChangeUID
1	123456	1	1	"Victor"	01/02/2016	roddawg
1	123456	1	2	"Hernandez"	01/02/2016	roddawg
1	7891011	1	1	"Sarah"	01/23/2016	jimbo
1	7891011	1	2	"Smith"	01/23/2016	jimbo
1	123456	2	1	"Tubular Piping"	01/02/2016	roddawg
1	123456	2	2	"$14.95"	01/02/2016	roddawg
1	7891011	2	1	"Hose Clamps"	01/23/2016	jimbo
1	7891011	2	2	"$0.18"	01/23/2016	jimbo

Screen 1 and Screen 2 are user defined screens.  Screen 1 contains Employee Names. Screen 2 contains products and prices.  So I brought the table into the model in the Data layer. In the Business Layer I have created a Query Subject for Employee Names.  This uses the above table as the datasource, filtered by Screen =1.  With the fields EMPID, Firstname (when field =1), Lastname (when field =2).

When left that way I get two records per EmployeeID. One for first name and one for last name, per EMPID.  I added a determinant with EMPID as the key and with Firstname and Lastname as attributes.  I checked the Group By box. 

Now when referencing these items in a list...if I only add Firstname and Lastname to the list everything works fine.  However when I add another item from say screen 3. I get no data. The item from Screen 3 is modeled very similarly as the above Firstname, Lastname.   I traced it down to probably a join issue.  So I looked in the business layer and joined the tables there.  I get a Warning stating by adding joins to this layer it invalidates any inherited joins from the data layer.  I agree to let it replicate those from the business layer. 
Now running the list again it will return data from both tables, however the Firstname/Lastname that was rolled up into a single line now is separated into two records again. Once with Firstname, Screen 3 data. Then again with Lastname, Screen 3 data.

	FirstName	Lastname	Screen3data
Current Output	Sarah		Cantaloupe
Current Output		Smith	Cantaloupe
Desired Output	Sarah	Smith	Cantaloupe

I've self taught Cognos entirely so I'm kind of stumped here.  How can I get it to display Firstname,Lastname, Screen3 data on a single record as shown on the last line of the table? Thank you for your time. Rodney

[roddawg]

Nobody?

[Lynn]

The best approach would be to use ETL processes to translate this table into separate tables. For example, an employee table with one row for each employee containing ID, first name and last name. Then a product table with one row per product with ID, name and price translated to a numeric value rather than a string. Even a database view that presents an appropriate structure would be an option if it performs adequately.

Failing that I'd avoid joins in the business layer and instead create separate aliases of the table in the physical layer for each element (first name, last name, etc) and put the filter condition as part of the join syntax. Then build your business query subject by pulling the appropriate elements from the appropriate alias of the table.

[roddawg]

I don't have the ability to use ETL on this data. Essentially I'm trying to process the table within the framework instead of externally. I'm can get the items into separate query subjects fine, it's just the join doesn't work when I attempt to use it.  Basically it doesn't know how to join data from itself.

[Lynn]

Failing that I'd avoid joins in the business layer and instead create separate aliases of the table in the physical layer for each element (first name, last name, etc) and put the filter condition as part of the join syntax. Then build your business query subject by pulling the appropriate elements from the appropriate alias of the table.

I addressed that in the second part of my earlier response. I don't think you need to join the table to itself. You need to create alias copies of the table for each specific screen/field situation and join each to your fact table using the appropriate filter criteria for each alias purpose.

[roddawg]

Thank you.  I'll attempt to fix it that way.  I was worried that is what I'd have to do, but as long as it works.