Simple Lookup

[Anonymous]

Hi Experts!

I'm looking to find a way to use lookup in Qlik.

I have 2 simple tables:

and this one

I want to use a sum function on value ID and using this I want to show Order in my straight table.

Please note that both are completely independent tables.

The result should look like this:

I've tried using FieldValue anf FieldIndex but it's not working.

Is this something possible?

Thankls

[sunny_talwar]

May be like this:

=Sum(If(Value = ValueID, Order))

[Anonymous]

This works fine but I need have an aggregation function like sum(ValueID) in order to be able to match it with Value.

The problem is in the data it's a whole number in my real application with I do sum(ValueID) it brings me decimal number

so for example it will give me 12.13 which is not equal to 12(Value) so it's not showing me the Order. I've tried to round the number by using Num(...,'#,##0') but it's not working.

I think this is where the problem is. Any idea how else I can round the sum function value?

[sravanthialuval]

Hi,

Try using Applymap

TAB:

MAPPING LOAD * INLINE [

    VALUE, ORDER

    3, 36

    7, 52

    8, 76

    9, 21

];

TAB1:

LOAD *,ApplyMap('TAB',VALUEID) AS ORDER INLINE [

    ID, VALUEID

    A, 3

    B, 7

    C, 8

    D, 9

];

[sunny_talwar]

Would you be able to update the sample?

[Anonymous]

I've attached the updated!

sum(ValueID) for A is 6.8 which  should match with the value 7 as rounded value of 6.8 is 7 and which should give Order 52 for A.

[sunny_talwar]

May be this:

=Sum(Aggr(If(Value = Round(sum(ValueID)), Order), ID, Value))