Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

girish_talele
Contributor

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.

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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, 

3 Replies
MVP
MVP

Re: Lookup from Matrix

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
Contributor

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).

Capture3.JPG

Please suggest way out in this case.

Inconvenience is regretted.

Regards,

Girish.

MVP
MVP

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, 

Community Browser