Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Creating Data for empty rows

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/3/17  A  200

1/4/17  A  200

1/5/17  A  200

1/6/17  A  200

1/7/17  A  400

etc...  etc...  etc...

Tags (2)
3 Replies
MVP
MVP

Re: Creating Data for empty rows

Not applicable

Re: Creating Data for empty rows

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  400

1/7/17 B  600

MVP
MVP

Re: Creating Data for empty rows

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;

Community Browser