topic Re: Converting the field value (Quantity) into interval values (Quantity_Range) in QlikView

Converting the field value (Quantity) into interval values (Quantity_Range)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Product	Quantity	Quantity_Range
ABC	5	0-5
ABC	10	6-15
ABC	5	16-20
ABC	12	21-32
ABC	3	33-35
ABC	5	35-40

I have a table with 2 fields (product and quantity). I need to create another field which will convert my quantities into a range. Please provide a solution

Re: Converting the field value (Quantity) into interval values (Quantity_Range)

tresB — Tue, 29 May 2018 08:45:02 GMT

Have a look at class()

Re: Converting the field value (Quantity) into interval values (Quantity_Range)

PrashantSangle — Tue, 29 May 2018 08:55:07 GMT

If your interval period is constant then you can use Class(),

If not then use if() to create bucket

like if(fieldName>0 and fieldName<6,'1-5',

if(fieldName>5 and fieldName<16,'6-15',

if(fieldName>15 and fieldName<21,'16-21','>21'

)))

Regards,

Re: Converting the field value (Quantity) into interval values (Quantity_Range)

jaumecf23 — Tue, 29 May 2018 09:00:23 GMT

Hi,

Attached you can find a possible solution.

Re: Converting the field value (Quantity) into interval values (Quantity_Range)

Anonymous — Tue, 29 May 2018 09:12:42 GMT

will the if condition be suitable? My quantity field values may vary. I need to set the Quantity_Range values with respect to the cumulative sum of the Quantities.

By using this i can calculate the Sum(Price) for the Quantities.

I need to get the following output:

Sales:

Product	Quantity	Quantity_Range	Price
ABC	5	0-5	520
ABC	10	6-15	1100
ABC	5	16-20	600
ABC	12	21-32	1800
ABC	3	33-35	450
ABC	5	35-40	750

where my price is calculated based on the pricing table:

Product:

PRODUCT	START	END	PRICE
ABC	1	3	100
ABC	4	15	110
ABC	16	30	120
ABC	31	40	150

I need to calculate the price based on the Quantities sold in sales table.

In Quantity=5, then Price = 100*3 (First 3 quantities) + 100*2 (remaining 2 quantites) = 520

For Quantity = 10, (It is considered as 15 (5+10) Quantities, but the pricing starts from 6-15 quantities, neglecting the pricing range of the first 5 quantities.) 110*10=1100.

Similarly i need to calculate the price of the remaining Quantity in the sales table.