2 Replies Latest reply: Dec 16, 2016 1:35 PM by Landon Wyant RSS

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

    Landon Wyant

      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

          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