Qlik Community

Qlik Sense App Development

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

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
MVP
MVP

Re: Generate Missing Values / Dummy Records

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";

2 Replies
MVP
MVP

Re: Generate Missing Values / Dummy Records

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

Re: Generate Missing Values / Dummy Records

Fantastic, Sunny, it works!

Thanks a lot =)

Community Browser