How to create intervals

[sculptorlv]

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

[sunny_talwar]

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?

[sculptorlv]

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

[sunny_talwar]

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"

[sculptorlv]

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

[Gabriel · Accepted solution]

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;

[sculptorlv]

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