Two fields in one table using the same dimension

[Anonymous]

We have two fields in one of our employee tables that uses the same lookup dimension and I can't figure out how to make it work within QlikView.  We track a 'CurrentLocation' and 'HomeLocation' and each field stores a 'LocationCode'.  What is my best bet for being able to use the same dimension field for two fields when they're stored in the same table?  I thought about trying to load the 'employeelocation' table twice but this caused a loop due to the unique person identifier.  My next attempt was to try and load the Locations table twice using a different naming convention to avoid loops, but this forces me to have two dimension tables.  Most of our dashboards we are only loading one of the two, but we had a new request that requires both current and home locations to be accessible.

Perhaps I'm making this more complicated than it needs to be, I just haven't figured out a way to make it work.  Here's a example of the data:

EmployeeLocations:

PersonID	StartDate	EndDate	CurrentLocation	HomeLocation
2332	1/1/17	5/1/17	TEX001	FLO001
2332	5/2/17		FLO001	FLO001
2442	5/1/17		FLO001	FLO001
2342	4/1/17	8/1/17	COL001	TEX001
2342	8/2/17		TEX001	TEX001

Locations:

LocationCode	LocationName
FLO001	Florida Site A
COL001	Colorado Site A
TEX001	Texas Site A

Thank you

[veidlburkhard]

Hi Phillip,

you can use ApplyMap twice to get two new fields CurLocationName and HomeLocationName.

Why don't you use the below script in order to get your result:

MapLocation:

Mapping

LOAD LocationCode,

     LocationName

FROM

[https://community.qlik.com/thread/268929]

(html, codepage is 1252, embedded labels, table is @2);

EmployeeLocations:

LOAD PersonID,

     StartDate,

     EndDate,

     CurrentLocation,

     HomeLocation,

     ApplyMap('MapLocation', CurrentLocation, '-') as CurLocationName,

     ApplyMap('MapLocation', HomeLocation, '-') as HomeLocationName

FROM

[https://community.qlik.com/thread/268929]

(html, codepage is 1252, embedded labels, table is @1);

Happy Qliking

Burkhard

[rubenmarin]

Hi Philip, I think the best option will be to have two dimensions.

If for any case you want only location to filter any location (current or home) you can create a composed key with current and home. Then create a table with a row for each location and combination, in example:

mLocations:

Mapping LOAD * INLINE [

LocationCode, LocationName

FLO001, Florida Site A

COL001, Colorado Site A

TEX001, Texas Site A

];

Data:

LOAD *, CurrentLocation &'#'& HomeLocation as keyLocation INLINE [

    PersonID, StartDate, EndDate, CurrentLocation, HomeLocation

    2332, 1/1/17, 5/1/17, TEX001, FLO001

    2332, 5/2/17, , FLO001, FLO001

    2442, 5/1/17, , FLO001, FLO001

    2342, 4/1/17, 8/1/17, COL001, TEX001

    2342, 8/2/17, , TEX001, TEX001

];

Locations:

LOAD keyLocation,

ApplyMap('mLocations', Location) as Location;

LOAD FieldValue('keyLocation', RecNo()) as keyLocation,

SubField(FieldValue('keyLocation', RecNo()), '#') as Location

AutoGenerate FieldValueCount('keyLocation');