topic Re: How do I number price-range periods? in QlikView

How do I number price-range periods?

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

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

Re: How do I number price-range periods?

sunny_talwar — Fri, 25 May 2018 15:43:57 GMT

May be this

AutoNumber(RowNo(), Product) as Period

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

Re: How do I number price-range periods?

sunny_talwar — Fri, 25 May 2018 15:45:24 GMT

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

];

Re: How do I number price-range periods?

olivierrobin — Fri, 25 May 2018 15:46:12 GMT

hello

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

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;

Re: How do I number price-range periods?

Anonymous — Fri, 25 May 2018 15:56:16 GMT

Great job Sunny!

Re: How do I number price-range periods?

leonardo_clavel — Fri, 25 May 2018 15:56:26 GMT

Thank you for your help!