topic Re: How to create intervals in QlikView

How to create intervals

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

Hello!

Hope for your help with a general task.

I have the Table_1:

Client Quantity Price

Sara 2 20 Eiro

Sara 4 15 Eiro

Sara 6 10 Eiro

I need to create additional table with intervals, like Table_2:

Client QuantityFrom QuantityTo Price

Sara 0 3.999 20

Sara 4 5.999 15

Sara 6 10000 10

Thank you in advance!

P.S.: it is a subtask for my general goal

Re: How to create intervals

sunny_talwar — Thu, 17 Jan 2019 12:09:46 GMT

The first row in the input table had a quantity of 2, why is the output beginning with 0 instead of 2? Also, the last row of output goes up to 10,000? Does it always stay at 10,000 or can this vary?

Re: How to create intervals

sculptorlv — Thu, 17 Jan 2019 12:11:35 GMT

mmm .. we just have such historical restricment in the system.
We can't put the 0 value to the quantity...
and 100000 represents any MAX value, in fact more or equal than 6 is 10 Eiro

Re: How to create intervals

sunny_talwar — Thu, 17 Jan 2019 12:15:17 GMT

Wait is the max 10,000 or 100,000? Previously you mentioned 10,000, but now you have 100,000? Also, what do you mean by this -> "in fact more or equal than 6 is 10 Eiro"

Re: How to create intervals

sculptorlv — Thu, 17 Jan 2019 12:42:35 GMT

My goal is to define the correct price for a client. The minimal price which he can get is 10 Eiro in case, when he puchae more (or equal) than 6 KG of a product.

In our case he will never get more than 10 000 (or 100 000) Kg of the product

Re: How to create intervals

Gabriel — Thu, 17 Jan 2019 13:21:10 GMT

Hi,

Try this solution

tmp:

LOAD * INLINE [
Client, Quantity , Price

Sara , 2 , 20 Eiro

Sara, 4 , 15 Eiro

Sara , 6 , 10 Eiro

];

NOCONCATENATE

tmp1:
LOAD
*,
IF(PREVIOUS(Quantity) > Quantity AND PREVIOUS(Quantity) <> Quantity OR ROWNO() =1,
NUM(Quantity - 0.01)) AS [NewField]

RESIDENT tmp
ORDER BY Price ASC
;

DROP TABLE tmp;

Re: How to create intervals

sculptorlv — Thu, 17 Jan 2019 13:29:51 GMT

Thanks, it will help me a lot ... I will only put comparison with the MAX and MIN value in order to put 0 or 100 000