Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

How to Generate missing Date

Hi All.

How to generate missing dates in QlikView. I have attached simple dataset for Reference.

Please any one give me solution.

Regards,

Vinod.

15 Replies
Highlighted
Contributor II

Re: How to Generate missing Date

Hi Vinod,

               Please reffer below link surely it will help,

Generate missing Dates

Thanks:-

Tushar

Highlighted
MVP
MVP

Re: How to Generate missing Date

Check if this document :Generating Missing Data In QlikView helps.

Highlighted
Contributor III

Re: How to Generate missing Date

Hi,

Please find the attached qvw file

Thanks,

Pooja

Highlighted
Partner
Partner

Re: How to Generate missing Date

Hi Vinod,

Try Below Code I got result  like below screenshot.

Directory;

LOAD

ID,

if(len(Date)=0,date(Previous(Date)+1,'DD-MM-YYYY'),DATE(Date,'DD-MM-YYYY')) as Date

 

FROM

[..\Downloads\DateData.xlsx]

(ooxml, embedded labels, table is Sheet1);


ans.PNG

Highlighted
New Contributor II

Re: How to Generate missing Date

ID column should not be null, It is also filled with ID no-9000 related to date 06-09-2018 and 10-09-2018.

Highlighted
New Contributor II

Re: How to Generate missing Date

ID column should not be null, It is also filled with ID no-9000 related to date 06-09-2018 and 10-09-2018.

Highlighted
Partner
Partner

Re: How to Generate missing Date

HI Vinod,

Try below.

Directory;

LOAD

if(len(ID)<0 or IsNull(ID) ,9000,ID)as  ID,

   

     if(len(Date)=0,date(Previous(Date)+1,'DD-MM-YYYY'),DATE(Date,'DD-MM-YYYY')) as Date

 

FROM

[..\Downloads\DateData.xlsx]

(ooxml, embedded labels, table is Sheet1);

answer.PNG

Highlighted
New Contributor II

Re: How to Generate missing Date

SORRY, I forget to tell you in my case it is 9000, but i want dynamic values.

Partner
Partner

Re: How to Generate missing Date

Hi Vinod

Dynamic means : Like 1 to 100