Generate Missing Values / Dummy Records

[#1]

Hi there

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

Facts:

Date	Dept	Val
01.01.2015	a	10
02.01.2015	a	20
10.01.2015	a	15
31.01.2015	a	7

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

[sunny_talwar · Accepted solution]

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

[#3]

Fantastic, Sunny, it works!

Thanks a lot 😃