Discussion board where members can get started with Qlik Sense.
I'm having trouble trying to generate data in between dates. For example, i have several dates
1/3/17 A 200
1/7/17 A 400
1/11/17 A 600
but I need
1/1/17 A 0
1/2/17 A 0
1/4/17 A 200
1/5/17 A 200
1/6/17 A 200
etc... etc... etc...
Have a look at
How to populate a sparsely populated field
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 2001/3/17 B 3001/5/17 A 400
1/7/17 B 600
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.
If(Peek(Letter) = Letter, Alt(Value, Peek(Value),0), Alt(Value,0) ) as Value,
ORDER BY Letter, Date;