3 Replies Latest reply: Nov 21, 2015 6:34 AM by Stefan Wühl RSS

    Lookup from Matrix

    Girish Talele

      Dear Experts,

      I have a query regarding lookup the value based on three parameters.

      We have a Matrix table & Order table as given below.

      Capture1.JPG

      In matrix table, a value is maintained against each Parameter (Param1,2,3) & for given Quantity Range.

      This Parameter Values needs to lookup in the Order table referring to the order qty.

      The required resulting table is as given below:-

      Capture2.JPG

      I guess, Using simple Mapping Load & ApplyMap it can not be done.

      Please suggest how to map this requirement.

       

      Regards,

      Girish.

        • Re: Lookup from Matrix
          Stefan Wühl

          You can do it using something along these lines:

           

           

          Matrix:
          LOAD * INLINE [
          From_Qty, To_Qty, Param1, Param2, Param3
          0, 10000, 30,15,15
          10001, 50000, 25,10,10
          50001, 100000, 15,5,5
          ];
          
          
          Orders:
          LOAD * INLINE [
          Ord_No,Ord_Qty, Field1, Field2
          1, 9000, Param1, 
          2,45000, Param1, Param2
          3,75000, Param1, Param3
          ];
          
          
          
          
          JOIN
          IntervalMatch (Ord_Qty) 
          LOAD From_Qty, To_Qty Resident Matrix;
          
          
          Left JOIN (Orders)
          LOAD * Resident Matrix;
          
          
          DROP TABLE Matrix;
          
          
          RESULT:
          LOAD Ord_No, Ord_Qty, Field1, Field2,
            pick(right(Field1,1), Param1, Param2, Param3) as FieldValue1,
            pick(right(Field2,1), Param1, Param2, Param3) as FieldValue2
          Resident Orders;
          
          
          DROP TABLE Orders;