Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have this king of table (with thousands of Materials):
i need to fill the missing Cost values folow the last known value.
for example: for 201101 to 201109, need to set value of 10.65 (folow what i had in 201012)
Is there any smart way to do it without a long long loop?
Material Number | Year | YearMM | Cost | |
---|---|---|---|---|
1 | ||||
110390 | 2010 | 201001 | 1 | |
110390 | 2010 | 201002 | 1 | |
110390 | 2010 | 201003 | 1 | |
110390 | 2010 | 201004 | 1 | |
110390 | 2010 | 201005 | 1 | |
110390 | 2010 | 201006 | 1 | |
110390 | 2010 | 201007 | 1 | |
110390 | 2010 | 201008 | 1 | |
110390 | 2010 | 201009 | 1 | |
110390 | 2010 | 201010 | 1 | |
110390 | 2010 | 201011 | 1 | |
110390 | 2010 | 201012 | 10.65 | 1 |
110390 | 2011 | 201101 | 1 | |
110390 | 2011 | 201102 | 1 | |
110390 | 2011 | 201103 | 1 | |
110390 | 2011 | 201104 | 1 | |
110390 | 2011 | 201105 | 1 | |
110390 | 2011 | 201106 | 1 | |
110390 | 2011 | 201107 | 1 | |
110390 | 2011 | 201108 | 1 | |
110390 | 2011 | 201109 | 1 | |
110390 | 2011 | 201110 | 18.65 | 1 |
110390 | 2011 | 201111 | 18.65 | 1 |
110390 | 2011 | 201112 | 18.65 | 1 |
Thanks in advance,
Tzur
Hi
You can use peek to check if the Material Number matches also like this:
if(isnull(Cost) AND [Material Number]=peek([Material Number]), peek(Cost), Cost) AS Cost
Regards
Matt
see the attached file
you can add more condition in if
hope this helps
Hi Sunil,
Thanks for your help, but i think i did not explain exactly what i need:
I need to implement it in the script in order to save it to QVD to reuse in other places.
i need something flexible, not hard coded, because for each material i can find different missing period.
Thanks
Hi Tzur
If you order the records in the table, you can then use the peek function to look at the previous record to populate to missing values. Something like this:
LOAD
[Material Number],
Year,
YearMM,
if(isnull(Cost), peek(Cost), Cost) AS Cost
FROM .........
ORDER BY [Material Number],YearMM;
Regards
Matt
Hi Matt,
Look like it's right direction, but i do have problem:
I have in this list many materials.
so i get this case:
on the right table, it "Before". on the left it "After" the manipulation.
as you can see, it took the last price from the previous Material to different material.
Any suggestion how to get over it?
Hi
You can use peek to check if the Material Number matches also like this:
if(isnull(Cost) AND [Material Number]=peek([Material Number]), peek(Cost), Cost) AS Cost
Regards
Matt
Hi ,
You should use
If(MaterialId=Peek(MaterialID) and Isnull(Cost),peek(Cost),Cost)
Matt / Vijay,
It works perfect!
Thank you very much.
I guess this line saved me thousands of lines if I would do it in loop....
Tzur
It will certainly be a lot faster than looping through it records.
Glad it works
Matt