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:
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:
Mapping LOAD * INLINE [
FLO001, Florida Site A
COL001, Colorado Site A
TEX001, Texas Site A
LOAD *, CurrentLocation &'#'& HomeLocation as keyLocation INLINE [