topic Dynamic Lookup using calculated measure in QlikView

Dynamic Lookup using calculated measure

Wed, 01 Mar 2017 12:48:40 GMT

Hi All,

I have a measure defined within the application with set analysis and based on the result (integer) i would like to get a corresponding values which is defined in a data mapping table.

Eg.

Data Mapping Table

LowestValue - HighestValue - Category

0 - 50 - A

51 - 100 - B

101 - 150 - C

151 - 200 - D

so on...

In the qlik sense application I would like to get the following table result where:

customer is a dimension value

value is a calculated measure

Category is a lookup result based on the interval cluster for value and the data mapping table

Customer - Value - Category

Andrea - 45 - A

Kris - 123 - C

Paul - 189 - D

In the above example Andrea's value, 45, is between 0 and 50 which is the A interval defined in the data mapping table.

I'm not able to lookup dynamically based an interval.

Any idea?

Thanks,

Antonello

Re: Dynamic Lookup using calculated measure

sunny_talwar — Wed, 01 Mar 2017 13:15:10 GMT

So, you don't want to do this in the script? May be using Aggr() function.... but this will take some toll on performance.

Re: Dynamic Lookup using calculated measure

Wed, 01 Mar 2017 13:20:03 GMT

Actually don't know if it's feasible. This is the expression for the dynamic measure:

sum(

aggr(

( Sum({<LEVEL={'Product'}>}UNIT*(1+Growth)) / Sum({<LEVEL={'Market'}>}UNIT*(1+Growth)) )

( Sum({<LEVEL={'Product'}>}total UNIT*(1+Growth)) / Sum({<LEVEL={'Market'}>}total UNIT*(1+Growth)) )

*100

PRD_WEIGHT

,Submarket,ISF)

)

Re: Dynamic Lookup using calculated measure

sunny_talwar — Wed, 01 Mar 2017 13:21:02 GMT

It might be difficult to propose much without a sample.... Would you be able to share a sample app and explain what your desired output is?

Re: Dynamic Lookup using calculated measure

Wed, 01 Mar 2017 14:23:42 GMT

I have attached an example.

The final result I would like to get is one more column which is goign to get corresponding Cluster value based on Area and the interval for the Value expression.

Should be

User	Area	Value	Cluster
Andrea	Custom	17	B
Andrea	Standard	11	A
Frank	Custom	23	B
Frank	Standard	23	A
Patrick	Custom	56	D
Patrick	Standard	45	C
Paul	Custom	87	D
Paul	Standard	67	D

Re: Dynamic Lookup using calculated measure

sunny_talwar — Wed, 01 Mar 2017 15:34:58 GMT

Try this:

Aggr(If(Sum({<Year={'2016'}>}Value) > Low and Sum({<Year={'2016'}>}Value) < High and Area = [Source Area], Cluster), User, Cluster, Area, [Source Area])

It might be helpful if you join Area and Source Area field because then you can will be able to just use this

Aggr(If(Sum({<Year={'2016'}>}Value) > Low and Sum({<Year={'2016'}>}Value) < High, Cluster), User, Cluster, Area)

Assuming you rename Source Area to Area

Re: Dynamic Lookup using calculated measure

Wed, 01 Mar 2017 16:37:49 GMT

Thanks... I will take some time to understand the solution but it works