Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
=(
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.
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...