Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
Creator III
Creator III

How to create intervals

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

1 Solution

Accepted Solutions
Gabriel
Partner - Specialist III
Partner - Specialist III

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;

 

difference in values.JPG

View solution in original post

6 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

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;

 

difference in values.JPG

sculptorlv
Creator III
Creator III
Author

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