Table with multiple detail dims on the same row

[JustinDallas]

Hello Folks,

I have a simple problem that I've solved a million times before, but that I've now forgotten how to.  Basically I have a fact table; Accidents, and a detail table Drivers. The fact and detail tables are linked by a join table.  Here is what it looks like as of now:

Acc #	=If( [Driver Role] = 'Driver 1' , DriverId )	=If( [Driver Role] = 'Driver 2' , DriverId )
1	JFK	--
1	--	AOL

What I would like is for both drivers to be on the same row, giving me something like this:

Acc #	DriverId1	DriverId2
1	JFK	AOL

Here is my test data if that helps:

BEGIN SCRIPT

Accidents:
LOAD *,
[Acc. #] AS '%accidentKey'
;
LOAD * Inline
[
'Acc. #', 'AccType','DriverId1','DriverId2'
1, 'Dinosaur Attack','JFK','AOL'
]
;

Drivers:
LOAD *,
DriverId AS '%driverId'
;
LOAD * Inline
[
'DriverId', 'FirstName'
'JFK','John',
'AOL','Abraham'
]
;

AccDriverJoinTable:
LOAD %accidentKey,
DriverId1 AS '%driverId',
'Driver 1' AS 'Driver Role'
Resident Accidents
;
LOAD %accidentKey,
DriverId2 AS '%driverId',
'Driver 2' AS 'Driver Role'
Resident Accidents
;

END SCRIPT

Any help on this super simple problem is greatly appreciated.

[Nicole-Smith]

The following code:

Accidents:
LOAD * INLINE [
	Acc. #, AccType, DriverId1, DriverId2
	1, Dinosaur Attack, JFK, AOL
];

Drivers:
LOAD * INLINE [
	DriverId, FirstName
	JFK, John
	AOL, Abraham
];

FOR i=1 TO 2
	LEFT JOIN (Accidents)
	LOAD DriverId AS DriverId$(i),
		 FirstName as DriverName$(i)
	RESIDENT Drivers;
NEXT

DROP TABLE Drivers;

Returns this result:

Acc. #	AccType	DriverId1	DriverName1	DriverId2	DriverName2
1	Dinosaur Attack	JFK	John	AOL	Abraham

Is that what you're looking for?

[JustinDallas]

=(

I was hoping to avoid datamodel work since the solution puts the Driver on the fact table, when the user clicks a value in Driver1, it will remove other fact rows that don't contain that driver as Driver1.  The way it currently stands with the DriverTable as a detail table, clicking the driver's name will bring in all accidents where they were Driver1 or Driver2.

[Nicole-Smith]

I'm not sure what the issue is then.

If I use your load script and make a table with three dimensions (Acc. #, DriverId1, DriverId2), it returns this: 

Acc #	DriverId1	DriverId2
1	JFK	AOL

Which is what you're asking for in your original post...