Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to fill missing values?

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

1 Solution

Accepted Solutions
Not applicable

Re: How to fill missing values?

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

8 Replies
chauhans85
Esteemed Contributor

Re: How to fill missing values?

see the attached file

you can add more condition in if

hope this helps

Not applicable

Re: How to fill missing values?

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

Not applicable

Re: How to fill missing values?

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

Not applicable

Re: How to fill missing values?

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?

exp.jpg

Not applicable

Re: How to fill missing values?

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
Valued Contributor

How to fill missing values?

Hi ,

You should use

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

Not applicable

How to fill missing values?

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

Not applicable

How to fill missing values?

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

Glad it works

Matt

Community Browser