What is the best way to use lookup tables in QlikView? -

in qlikview, have table data , 1 database table a. table a should used twice (a_left, a_right). (table can have thousands of entries.)

my load script is:

a_left: load a_id_left,      a_name_left inline [   a_id_left, a_name_left   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ];  a_rigtht: load a_id_right,      a_name_right inline  [   a_id_right, a_name_right   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ];  data: load id,       a_id_left,      a_name_left 'name_left',      a_id_right,      a_name_right 'name_right',      data inline [   id, a_id_left, a_right_id, data   1, 1, 2, 37   1, 1, 3, 18   1, 2, 3, 62 ]; 

so question is: best way use lookup tables in qlikview? (should use mapping and/or applymap? why? faster?)

one other part of question is: change data structure star table? (i know cost more memory.) and, way: how put data in 1 table can store in 1 qvd file?

thanks ideas.

for simple lookups wish single value value can use mapping load , use applymap() function. example, have following table:

load    * inline [    userid, system    1, windows    2, linux    3, windows ]; 

i have table contains userid , username follows:

load    * inline [    userid, username    1, alice    2, bob    3, carol ]; 

i can combine above tables applymap follows:

usernamemap: mapping load    * inline [    userid, username    1, alice    2, bob    3, carol ];  systemdata: load    userid,    applymap('usernamemap', userid, 'missing') username,    system inline [    userid, system    1, windows    2, linux    3, windows ]; 

applymap fast , should not slow down load time (although not fast direct qvd load). however, mentioned applymap can used if wish map single value table. more fields, need use join (which similar sql join) if wish combine results single table.

if not wish join them single table (but keep "star" scheme), make sure fields wish link named same. example:

a_left: load a_id_left,      a_name_left [name_left] inline [   a_id_left, a_name_left   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ];  a_rigtht: load a_id_right,      a_name_right [name_right] inline  [   a_id_right, a_name_right   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ];  data: load id,       a_id_left,      a_id_right,      data inline [   id, a_id_left, a_right_id, data   1, 1, 2, 37   1, 1, 3, 18   1, 2, 3, 62 ]; 

(i have removed "name" fields "data" fail load).

this work in qlikview document due qlikview's automatic field associativity.

however, if wish have data in single table (e.g. output qvd) in case need join 2 tables data. can rearrange of tables make our life bit easier, if put data table first, can join other 2 tables on:

data: load id,       a_id_left,      a_id_right,      data inline [   id, a_id_left, a_id_right, data   1, 1, 2, 37   1, 1, 3, 18   1, 2, 3, 62 ];  left join (data) load a_id_left,      a_name_left [name_left] inline [   a_id_left, a_name_left   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ];  left join (data) load a_id_right,      a_name_right [name_right] inline  [   a_id_right, a_name_right   1, 'nwsnd'   2, 'dcsdcws'   3, 'fsdf'    ]; 

this resort in single table named "data" can output qvd etc.

you may wish think optimising "table a" extract since being loaded twice, may take time (e.g. long distance server etc.) may better grab data in 1 go , slice once it's in memory (much faster). quick example below:

tablea: load      a_id_left,      a_id_right,      a_name_left,      a_name_right ...;  data: load id,       a_id_left,      a_id_right,      data inline [   id, a_id_left, a_id_right, data   1, 1, 2, 37   1, 1, 3, 18   1, 2, 3, 62 ];  left join (data) load distinct      a_id_left,      a_name_left [name_left] resident tablea;  left join (data) load distinct      a_id_right,      a_name_right [name_right] resident tablea;  drop table tablea;