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.

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:-

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

Please suggest how to map this requirement.

Regards,

Girish.

[swuehl]

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;

[girish_talele]

Dear Swuehl,

Thanks for quick reply, your solution gives perfect results as per the given example.

But the catch is the Parameter Names may not have number as I mentioned in example, it may be any text (refer below snap).

Please suggest way out in this case.

Inconvenience is regretted.

Regards,

Girish.

[swuehl · Accepted solution]

Just replace

pick(right(Field1,1), Param1, Param2, Param3) as FieldValue1, 

with

pick(match(Field1,'X','Y','Z'), X, Y, Z) as FieldValue1,