Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iamscribe
Contributor III
Contributor III

Two fields in one table using the same dimension

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:

PersonIDStartDateEndDateCurrentLocationHomeLocation
23321/1/175/1/17TEX001FLO001
23325/2/17FLO001FLO001
24425/1/17FLO001FLO001
23424/1/178/1/17COL001TEX001
23428/2/17TEX001TEX001

Locations:

LocationCodeLocationName
FLO001Florida Site A
COL001Colorado Site A
TEX001Texas Site A

Thank you

2 Replies
veidlburkhard
Creator III
Creator III

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');