Join second table to first table based on condition from the first table

[#1]

Hi! I am relatively new to Qlikview and would appreciate any help. Thanks in advance!

I am attempting to join 2 tables together to assign a [Process Time] to each transaction line based upon what WAREHOUSE they were picked in. Currently, the closest I can get is to assign each [Process Time] as a new field:

Is there a way to do this so that only 1 [Process Time] is available? I have included an example of one months data and my current lookup sheet if it is needed.

[marcus_sommer · Accepted solution]

Quite probably I wouldn't do it in this way. Why splitting WAREHOUSE into several fields and creating so many fields more within your temp-table-chain - at the first glance it looked  more complicated than it must be.

I suggest to add the process time as one field with a mapping (which is prepared per crosstable), maybe in this way:

t0:

crosstable(WAREHOUSE, VALUE, 2)

load Function, [Process Time], ARN, LAC, MEP inline [

Function    ARN    LAC    MEP    Process Time

Alias Issue / Receipt    1    1    1    00:00:00

LPN Pack    1    1    1    00:00:30

LS/ BULK-LS Pick    1    1    1    00:01:17

LTL/ LS Pack    1    1    1    00:03:21

No Function Defined    1    1    1    00:00:00

Oversize Pack    1            00:03:15

Oversize Pack        1    1    00:01:03

Oversize Pick    1    1    1    00:01:28

Parcel Pack    1    1        00:01:13

Parcel Pack            1    00:00:30

PO Receipt:     1    1    1    00:02:20

PO Receipt: MOD 0    1    1    1    00:00:30

PO Receipt: MOD 1    1    1    1    00:05:52

] (txt, delimiter is \t);

t1:

mapping load Function & '|' & WAREHOUSE as Key, [Process Time];

load * resident t0 where VALUE = 1;

Transactions:

load *, applymap('t1', Function & '|' & WAREHOUSE, 'YourDefaultValue') as [Process Time]

from YourSource;

drop table t0;

- Marcus

[#3]

I couldn't get the above to work (the cross table), but it may just be a lack of knowledge, experience, etc. on my part. The Mapping with a Key field is an excellent idea! Because of that, I was able to get it to work correctly. Thanks so much for the assistance!