topic Re: How to accees a table using a dynamic key in QlikView

How to accees a table using a dynamic key

Mon, 24 Aug 2015 16:57:06 GMT

I am trying to recover a value from an inline table similar to how you would do it with a VLOOKUP in Excel, but in a QV dashboard. I believe I cannot use "Lookup" or "ApplyMap" as the calculation cannot be done in the script as the user choice is not predictable. The user makes a selection (customer, timeframe let's say) which creates an averaged value. This value has a corresponding multiplier contained in a two column table, which is the value I want to recover in order to use in a further calculation.

Example, the user selects customer = ABC & date range = 2104 & 2015. This gives (say) a month average of 43 months, which corresponds in an inline table (below) to 7.5%. A subsequent value then needs to be multiplied by this 7.5%.

41, 6.5%

42, 7.0%

43, 7.5%

44, 8.0%

45, 8.5%

...

How do I dynamically access such a table from within an object and retrieve the 7.5%?

An "IF" statement is not a possible solution as this would make the overall calculation far too complex and unmanageable i.e. not this type of logic:-

IF month_avg = 42 then use 7.0%

IF month_avg = 43 then use 7.5%

Help appreciated,

Nick

Re: How to accees a table using a dynamic key

kaushiknsolanki — Mon, 24 Aug 2015 17:15:09 GMT

Hi,

This seems to be possible, but for that you need to answer me one things.

Whether the MonthDifference (Inline Table) is one for all other selections, meaning whether that applies to all kind of customer, products and other filters?

Give a sample data if possible.

Regards,

Kaushik Solanki

Re: How to accees a table using a dynamic key

ramoncova06 — Mon, 24 Aug 2015 17:39:05 GMT

this is pretty raw but it should give you an idea of how to achieve this by using a variable

Re: How to accees a table using a dynamic key

Mon, 24 Aug 2015 18:17:58 GMT

Hi Kaushik,

Yes it's the same table of data for all customers.

Nick

Re: How to accees a table using a dynamic key

kaushiknsolanki — Mon, 24 Aug 2015 18:19:30 GMT

Good,

You can try your hand with the solution provided by Ramon.

If not let me know. I will give you that, but I want to make sure that you give a try.

Regards,

Kaushik Solanki

Re: How to accees a table using a dynamic key

Mon, 24 Aug 2015 18:23:50 GMT

Hi Ramon,

Yes, thanks, I'll give that a try, it looks very promising, many thanks.

Regards,

Nick

Re: How to accees a table using a dynamic key

Mon, 24 Aug 2015 18:42:57 GMT

Ramon,

Many thanks.....works perfectly.

All the best,

Nick

Re: How to accees a table using a dynamic key

ramoncova06 — Mon, 24 Aug 2015 19:04:29 GMT

glad it helped