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: 
gidon500
Creator II
Creator II

Adjust cells

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

    

PNYearMonthPRICE
10001201610
10002201620
10003201630
10004201640
10005201650
10006201660
200012016110
200022016120
200032016130
200042016140
200052016150
2000620160
300012016210
300022016220
300032016230
300042016240
300052016250
3000620160
400012016310
400022016320
400032016330
400042016340
400052016350
400062016360
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
m_woolf
Master II
Master II

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;

sunny_talwar

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;

sunny_talwar

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

gidon500
Creator II
Creator II
Author

thanks

for your fast response

gidon