Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
This might now work if two consecutive Months have 0 as PRICE.
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;
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;
This might now work if two consecutive Months have 0 as PRICE.
thanks
for your fast response
gidon