Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Valued Contributor II

Table with multiple detail dims on the same row

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 )
1JFK--
1--

AOL

 

 

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

Acc #DriverId1DriverId2
1JFKAOL

 

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.

 

 

Labels (3)
3 Replies
Highlighted

Re: Table with multiple detail dims on the same row

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 
1Dinosaur AttackJFKJohnAOLAbraham

 

Is that what you're looking for?

Highlighted
Valued Contributor II

Re: Table with multiple detail dims on the same row

=(

 

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.

 

 

Highlighted

Re: Table with multiple detail dims on the same row

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 #DriverId1DriverId2
1JFKAOL


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