Lookup function

Question & Answer
,
1

Problem statement: Lookup function is not present .
Workaround: I used LAC-W function and derived value .
We need to use that derived value and derived value from another field. But am facing the following error.

RTG_Look = sumOver(sum(marketvalue*rtgmapindx))/sumOver(sum(marketvalue)) – output is 6 . RTG_Look (value 6 )need to used as input and return value from another field .I am trying to create a calculated field but not supporting the nested LAC-w

Appreciate your inputs on this.

2

What is the lookup calculation?

You could look to use PRE_AGG to your functions.

median(sumOver({abandon_time},[{queue_Name}],PRE_AGG))

Here is one that works for me.

3

Do you mean the equivalent of VLOOKUP in Excel? There isn’t a function like that in QuickSight but you may be able to use the ifelse function as workaround.

Field C
= ifelse({Field A} = 6, {Field B}, null)

2 Likes
4

Thank you David. I tried if its constant as mentioned in example i.e 6 it works but am using the calculated field i,e (RTG_Look = sumOver(sum(marketvalue*rtgmapindx))/sumOver(sum(marketvalue)))

Field C
= ifelse({Field A} = 6, {Field B}, null) - it works

Field C
= ifelse({Field A} = RTG_Look, {Field B}, null) - am getting error i.e

[Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination]

5

Do you need the sumOver function? You can’t mix sumOver and sum in the same calculation. Do you get the right result for RTG_Look if you just change it to sum(marketvalue*rtgmapindx)/sum(marketvalue)?

2 Likes
6

David, Yes we will get correct result, instead of multiplying in quicksight i did at backend in SQL and able to get the required Lookup value(6).