Discussion Board for collaboration on QlikView Scripting.
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?
Thanks in advance,
Go to Solution.
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
see the attached file
you can add more condition in if
hope this helps
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.
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:
if(isnull(Cost), peek(Cost), Cost) AS Cost
ORDER BY [Material Number],YearMM;
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?
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....
It will certainly be a lot faster than looping through it records.
Glad it works