topic Re: Populate missing date in QlikView

Populate missing date

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Populate missing date

qlikviewwizard — Mon, 06 Aug 2018 02:56:28 GMT

Hi,

Please check these links:

How to populate a sparsely populated field

Generating Missing Data In QlikView

Re: Populate missing date

Anil_Babu_Samineni — Mon, 06 Aug 2018 04:27:21 GMT

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;

Re: Populate missing date

ChennaiahNallani — Mon, 06 Aug 2018 05:06:12 GMT

create master calendar.

Master Calendar Script

Re: Populate missing date

thannila — Mon, 06 Aug 2018 06:06:08 GMT

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;