Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this:
=Sum(If(Value = ValueID, Order))
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?
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
];
Would you be able to update the sample?
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.
May be this:
=Sum(Aggr(If(Value = Round(sum(ValueID)), Order), ID, Value))