-
Re: Creating Data for empty rows
Stefan Wühl May 19, 2017 4:25 PM (in response to Michael Yuen )Have a look at
-
Re: Creating Data for empty rows
Michael Yuen May 19, 2017 6:10 PM (in response to Stefan Wühl )Hi Stefan,
That helps a lot and it's a good start. The problem I'm running into now is when I have another letter in the same column. For instance:
1/1/17 A 200
1/3/17 B 300
1/5/17 A 4001/7/17 B 600
-
Re: Creating Data for empty rows
Stefan Wühl May 19, 2017 6:21 PM (in response to Michael Yuen )It should all be explained in the technical brief that is linked to Henric's blog post:
Generating Missing Data In QlikView
Basically, you need to create a table with all combinations of letters and dates needed and join this to your fact table.
Then do a resident load, sorted by letter and dates ascending and use Peek() to retrieve the previous value, resetted when letter changes.
LOAD
Letter,
Date,
If(Peek(Letter) = Letter, Alt(Value, Peek(Value),0), Alt(Value,0) ) as Value,
...
RESIDENT AllCombinationTable
ORDER BY Letter, Date;
-
-