How do I number price-range periods?

[leonardo_clavel]

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.

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

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

Product	Price_ID	FromDate	ToDate	Price	Period
Prod_AAA	10001	01/01/2016	31/03/2017	15.8	1
Prod_AAA	10050	01/04/2017	30/11/2017	16.6	2
Prod_AAA	10090	01/12/2017	31/04/2018	17.8	3
Prod_BBB	10020	01/02/2017	31/05/2017	32.5	1
Prod_BBB	10040	01/06/2017	31/10/2017	34.8	2

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

[sunny_talwar]

May be this

AutoNumber(RowNo(), Product) as Period

or

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

[sunny_talwar · Accepted solution]

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]

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]

Great job Sunny!

[leonardo_clavel]

Thank you for your help!