topic Re: How to update the value based on a range in another table? in QlikView

How to update the value based on a range in another table?

Fri, 20 Nov 2020 18:02:30 GMT

There are two tables.

1.Commision percentage based on the max and min ranges of the price of the product.

2.Sales info.

The problem is I have to calculate the commission amount based on the values in table 1.We cant hard-code it because the percentage may change in table1.

Re: How to update the value based on a range in another table?

ankitaag — Tue, 26 May 2015 12:14:44 GMT

Hi,

Can you provide some sample data and tell what you exactly wanna calculate.

Re: How to update the value based on a range in another table?

Tue, 26 May 2015 14:54:17 GMT

Suppose the Commission percentages are:

Low Value Max Value Percentage

0 100000 2

100001 500000 2.5

500001 1000000 3

And I have given a table which contains various cost prices.Now the objective is to calculate the commission amount using these values.And one constraint,these values should not be hard-coded.That means these values may be changed.I heard something called dynamic bucketing using macros.Does that help?If yes,then how can you upload fields from the sheet to the macro?

Re: How to update the value based on a range in another table?

Tue, 26 May 2015 15:07:19 GMT

And I will also provide some sample data for which I need to calculate the commission values.

Sales Id Cost

1 200000

2 300000

3 670000

4 950000

5 750000

And the problem is no of buckets may also change,so is there a way in which the data in excel files(where the data is changed) can be loaded as fields into the macro and corresponding updates are done when the data is reloaded.

I have also seen one post on dynamic bucketing,is this helpful in solving my problem?The problem I am facing is how to upload fields from the sheet into the macro and dividing into buckets.

http://www.infinityinsight.com/blog/?p=221

Re: How to update the value based on a range in another table?

MarcoWedel — Tue, 26 May 2015 19:46:14 GMT

‌search for intervalmatch

regards

Marco

Re: How to update the value based on a range in another table?

Tue, 26 May 2015 20:22:31 GMT

Hi Marco,

Thanks a lot.It worked

Re: How to update the value based on a range in another table?

MarcoWedel — Tue, 26 May 2015 21:03:14 GMT

Glad it helped.

please close your thread if your question is answered.

thanks

regards

Marco

Re: How to update the value based on a range in another table?

Wed, 27 May 2015 17:19:16 GMT

Hi Marco,

I would like to ask you more about this interval match.Suppose I want to check the interval for a changing variable in the sheet itself.

For example,Take sales ID's 1,2,3,4 .Suppose they belong to January.I would like to check the interval based on the sum of the costs of those sales ID's.i.e.,I should check the interval based on a variable that changes according to the selections.Can we do that without using macros?

Re: How to update the value based on a range in another table?

MarcoWedel — Wed, 27 May 2015 18:26:02 GMT

Hi,

please post some sample data and your expected result related to selections made (if possible in a new thread).

thanks

regards

Marco

Re: How to update the value based on a range in another table?

Wed, 27 May 2015 18:46:27 GMT

Hi,

I have started a new Thread and attached some files for examples.

How to update the value based on a range in the another table or variable?

Thanks and Regards,

Srinath