topic Re: Join second table to first table based on condition from the first table in QlikView

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

Thu, 15 Dec 2016 15:42:36 GMT

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.

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

marcus_sommer — Fri, 16 Dec 2016 12:05:11 GMT

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

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

Fri, 16 Dec 2016 18:35:55 GMT

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!