topic Re: How to fill missing values? in QlikView

How to fill missing values?

Tue, 03 Apr 2012 08:10:27 GMT

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

Re: How to fill missing values?

SunilChauhan — Tue, 03 Apr 2012 08:23:16 GMT

see the attached file

you can add more condition in if

hope this helps

Re: How to fill missing values?

Tue, 03 Apr 2012 08:30:11 GMT

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

Re: How to fill missing values?

Tue, 03 Apr 2012 08:47:43 GMT

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

Re: How to fill missing values?

Tue, 03 Apr 2012 09:32:58 GMT

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?

Re: How to fill missing values?

Tue, 03 Apr 2012 09:37:29 GMT

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

How to fill missing values?

vijay_iitkgp — Tue, 03 Apr 2012 09:38:12 GMT

Hi ,

You should use

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

How to fill missing values?

Tue, 03 Apr 2012 11:05:47 GMT

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

How to fill missing values?

Tue, 03 Apr 2012 11:20:20 GMT

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

Glad it works

Matt