Populate missing date

[viveksingh]

Hi All,

i have data for only weekday and no data on weekend. Nd the my calendar date fiel doesn’t have weekend dates.

Here is sample

salesdate	sales
1/8/2018	100
2/8/2018	50
3/8/2018	200
6/8/2018	60
7/8/2018	310

‌

if you observe above data, there is no data for weekends. And even if any date is missing then it should populate date nd sales should be 0 for those missing one.

[qlikviewwizard]

Hi,

Please check these links:

How to populate a sparsely populated field

Generating Missing Data In QlikView

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

NullAsValue sales;

Set NullValue = '0';

Sales:

Load * Inline [

salesdate, sales

1/8/2018, 100

2/8/2018, 50

3/8/2018, 200

6/8/2018, 60

7/8/2018, 310

];

Load Date(MinDate + IterNo() -1 ) AS salesdate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(salesdate) AS MinDate,

    Max(salesdate) AS MaxDate

RESIDENT Sales;

[ChennaiahNallani]

create master calendar.

Master Calendar Script

[thannila]

DataSource:

load * Inline

[

salesdate, sales

1/8/2018, 100

2/8/2018, 50

3/8/2018, 200

6/8/2018, 60

7/8/2018, 310

];

Data:

Load Date(MinDate + IterNo() -1 ) AS salesdate

While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(salesdate) AS MinDate,

    Max(salesdate) AS MaxDate

RESIDENT DataSource;

LEFT JOIN(Data)

LOAD * Resident DataSource;

drop table DataSource;

NoConcatenate

Final:

load salesdate

,if(isnull(sales),0,sales)as sales

resident Data;

Drop Table Data;