Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
waqqas2426
Contributor

Simple Lookup

Hi Experts!

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

I have 2 simple tables:

tav1.PNG

and this one

tav2.PNG

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:

tav3.PNG

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

Is this something possible?

Thankls

Tags (2)
6 Replies
Highlighted

Re: Simple Lookup

May be like this:

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


Capture.PNG

Highlighted
waqqas2426
Contributor

Re: Simple Lookup

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?

Highlighted
sravanthialuval
Contributor

Re: Simple Lookup

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

];

Capture.PNG

Highlighted

Re: Simple Lookup

Would you be able to update the sample?

Highlighted
waqqas2426
Contributor

Re: Simple Lookup

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.

Highlighted

Re: Simple Lookup

May be this:Capture.PNG

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