topic Re: Simple Lookup in QlikView

Simple Lookup

Anonymous — Mon, 27 Mar 2017 14:09:53 GMT

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

Re: Simple Lookup

sunny_talwar — Mon, 27 Mar 2017 14:20:51 GMT

May be like this:

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

Re: Simple Lookup

Anonymous — Mon, 27 Mar 2017 14:53:28 GMT

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?

Re: Simple Lookup

sravanthialuval — Mon, 27 Mar 2017 14:57:01 GMT

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

];

Re: Simple Lookup

sunny_talwar — Mon, 27 Mar 2017 15:03:11 GMT

Would you be able to update the sample?

Re: Simple Lookup

Anonymous — Mon, 27 Mar 2017 15:29:46 GMT

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.

Re: Simple Lookup

sunny_talwar — Mon, 27 Mar 2017 15:33:38 GMT

May be this:

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