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: 
leonardo_clavel
Contributor
Contributor

How do I number price-range periods?

Dear community,

I'd be grateful if someone could help me with the following situation

I have the following price list, with diferent date ranges.

Already ordered by date.

ProductPrice_IDFromDateToDatePrice
Prod_AAA1000101/01/201631/03/201715.8
Prod_AAA1005001/04/201730/11/201716.6
Prod_AAA1009001/12/201731/04/201817.8
Prod_BBB1002001/02/201731/05/201732.5
Prod_BBB1004001/06/201731/10/201734.8

I need to number price-range periods for  each product, as shown below

ProductPrice_IDFromDateToDatePricePeriod
Prod_AAA1000101/01/201631/03/201715.81
Prod_AAA1005001/04/201730/11/201716.62
Prod_AAA1009001/12/201731/04/201817.83
Prod_BBB1002001/02/201731/05/201732.51
Prod_BBB1004001/06/201731/10/201734.82

Find as follows script to generate above example

Price_records:

load * inline [

Produt, Price_ID, FromDate, ToDate, Price

Prod_AAA, 10001, 01/01/2016, 31/03/2017, 15.8

Prod_AAA, 10050, 01/04/2017, 30/11/2017, 16.6

Prod_AAA, 10090, 01/12/2017, 31/04/2018, 17.8

Prod_BBB, 10020, 01/02/2017, 31/05/2017, 32.5

Prod_BBB, 10040, 01/06/2017, 31/10/2017, 34.8];

Thank you in advance for your help

Leonardo

1 Solution

Accepted Solutions
sunny_talwar

Example script

Price_records:

LOAD *,

If(Product = Previous(Product), RangeSum(Peek('Period'), 1), 1) as Period,

AutoNumber(RowNo(), Product) as Period1;

LOAD * INLINE [

    Product, Price_ID, FromDate, ToDate, Price

    Prod_AAA, 10001, 01/01/2016, 31/03/2017, 15.8

    Prod_AAA, 10050, 01/04/2017, 30/11/2017, 16.6

    Prod_AAA, 10090, 01/12/2017, 31/04/2018, 17.8

    Prod_BBB, 10020, 01/02/2017, 31/05/2017, 32.5

    Prod_BBB, 10040, 01/06/2017, 31/10/2017, 34.8

];

View solution in original post

5 Replies
sunny_talwar

May be this

AutoNumber(RowNo(), Product) as Period

or

If(Product = Previous(Product), RangeSum(Peek('Period'), 1), 1) as Period

sunny_talwar

Example script

Price_records:

LOAD *,

If(Product = Previous(Product), RangeSum(Peek('Period'), 1), 1) as Period,

AutoNumber(RowNo(), Product) as Period1;

LOAD * INLINE [

    Product, Price_ID, FromDate, ToDate, Price

    Prod_AAA, 10001, 01/01/2016, 31/03/2017, 15.8

    Prod_AAA, 10050, 01/04/2017, 30/11/2017, 16.6

    Prod_AAA, 10090, 01/12/2017, 31/04/2018, 17.8

    Prod_BBB, 10020, 01/02/2017, 31/05/2017, 32.5

    Prod_BBB, 10040, 01/06/2017, 31/10/2017, 34.8

];

olivierrobin
Specialist III
Specialist III

hello

I think this could help (assuming rows are in the correct order)

t:

NoConcatenate load

Produt,min(RecNo()) as minrn

resident

Price_records

group by Produt

;

left join(Price_records)

load Produt,minrn

resident t;

t2:

NoConcatenate

load *,recno()- minrn+1 as Period

resident Price_records;

drop table t,Price_records;

Anonymous
Not applicable

Great job Sunny!

leonardo_clavel
Contributor
Contributor
Author

Thank you for your help!