Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
waqqas2426
Creator
Creator

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

6 Replies
sunny_talwar

May be like this:

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


Capture.PNG

waqqas2426
Creator
Creator
Author

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

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

sunny_talwar

Would you be able to update the sample?

waqqas2426
Creator
Creator
Author

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:Capture.PNG

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