Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generate Missing Values / Dummy Records

Hi there

Have the following table, which I want to fill with zero-values for all missing Dates.

Facts:

DateDeptVal
01.01.2015a10
02.01.2015a20
10.01.2015a15
31.01.2015a7

There's also a calendar table with every possible date, both of the tables are connected via a date ID. So for any date in the calendar table combinated with any dept in the Facts table, I want Val to be 0 if doesn't exist in the Facts table yet.

Hope my question is understandable and hope anyone can help 🙂

Kind regards

Michael

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table:

LOAD "Date",

    Dept,

    Val

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Date("Date" + IterNo() - 1) as "Date",

  Dept,

    If(Date = Date("Date" + IterNo() - 1), Val, 0) as Val

While "Date" + IterNo() - 1 <= EndDate;

LOAD *,

  If(Dept = Previous(Dept), Previous("Date") - 1, Today()) as EndDate

Resident Table

Order By Dept, "Date";

View solution in original post

2 Replies
sunny_talwar

May be this:

Table:

LOAD "Date",

    Dept,

    Val

FROM [lib://Lib]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Date("Date" + IterNo() - 1) as "Date",

  Dept,

    If(Date = Date("Date" + IterNo() - 1), Val, 0) as Val

While "Date" + IterNo() - 1 <= EndDate;

LOAD *,

  If(Dept = Previous(Dept), Previous("Date") - 1, Today()) as EndDate

Resident Table

Order By Dept, "Date";

Not applicable
Author

Fantastic, Sunny, it works!

Thanks a lot 😃