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

# Lookup from Matrix

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.

• ###### Re: Lookup from Matrix

You can do it using something along these lines:

```Matrix:
From_Qty, To_Qty, Param1, Param2, Param3
0, 10000, 30,15,15
10001, 50000, 25,10,10
50001, 100000, 15,5,5
];

Orders:
Ord_No,Ord_Qty, Field1, Field2
1, 9000, Param1,
2,45000, Param1, Param2
3,75000, Param1, Param3
];

JOIN
IntervalMatch (Ord_Qty)

Left JOIN (Orders)

DROP TABLE Matrix;

RESULT:
pick(right(Field1,1), Param1, Param2, Param3) as FieldValue1,
pick(right(Field2,1), Param1, Param2, Param3) as FieldValue2
Resident Orders;

DROP TABLE Orders;
```
• ###### Re: Lookup from Matrix

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.

• ###### Re: Lookup from Matrix

Just replace

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

with

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