topic Re: Adjust cells in QlikView

Adjust cells

gidon500 — Wed, 08 Jun 2016 16:40:48 GMT

Hi guys

I have a issue that needs help

I get price list for products for each month

in some cases , if the price is 0 I need to get the last know price

as you can see PN 200 for month 062016 is 0 , in this case I need to load it as 150 as it is the price for 052016

thanks for your help

gidon

PN	YearMonth	PRICE
100	012016	10
100	022016	20
100	032016	30
100	042016	40
100	052016	50
100	062016	60
200	012016	110
200	022016	120
200	032016	130
200	042016	140
200	052016	150
200	062016	0
300	012016	210
300	022016	220
300	032016	230
300	042016	240
300	052016	250
300	062016	0
400	012016	310
400	022016	320
400	032016	330
400	042016	340
400	052016	350
400	062016	360

Re: Adjust cells

m_woolf — Wed, 08 Jun 2016 16:46:29 GMT

TableTmp:

load

PN,

YearMonth,

Price

resident Whatever

order by PN, YearMonth;

Drop table Whatever;

Table:

noconcatenate load

PN,

YearMonth,

if(PN = Peek('PN') and Price = 0,Peek('Price'),Price) as Price

resident TableTemp;

Drop TableTemp;

Re: Adjust cells

sunny_talwar — Wed, 08 Jun 2016 16:48:03 GMT

Try this:

Table:

LOAD PN,

Date(Date#(YearMonth, 'MMYYYY'), 'MMYYYY') as YearMonth,

PRICE

FROM

[https://community.qlik.com/thread/220044]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD PN,

YearMonth,

If(PRICE = 0 and PN = Previous(PN), Peek('NEW_PRICE'), PRICE) as NEW_PRICE

Resident Table

Order By PN, YearMonth;

DROP Table Table;

RENAME Field NEW_PRICE TO PRICE;

Re: Adjust cells

sunny_talwar — Wed, 08 Jun 2016 16:49:10 GMT

This might now work if two consecutive Months have 0 as PRICE.

Re: Adjust cells

gidon500 — Wed, 08 Jun 2016 17:28:21 GMT

thanks

for your fast response

gidon