topic Re: Generate Missing Values / Dummy Records in App Development

Generate Missing Values / Dummy Records

Tue, 19 Jul 2016 14:37:42 GMT

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

Re: Generate Missing Values / Dummy Records

sunny_talwar — Tue, 19 Jul 2016 16:43:55 GMT

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

Re: Generate Missing Values / Dummy Records

Wed, 20 Jul 2016 07:20:46 GMT

Fantastic, Sunny, it works!

Thanks a lot 😃