How to fill missing values?

[#1]

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

[SunilChauhan]

see the attached file

you can add more condition in if

hope this helps

[#3]

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

[#4]

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

[#5]

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?

[#6 · Accepted solution]

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

[vijay_iitkgp]

Hi ,

You should use

If(MaterialId=Peek(MaterialID) and Isnull(Cost),peek(Cost),Cost)

[#8]

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

[#9]

It will certainly be a lot faster than looping through it records.

Glad it works

Matt