Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Creator II
Creator 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.

Creator III
Creator 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
Contributor II
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
Contributor II
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
Contributor II
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.

Highlighted
Partner
Partner

Re: How to Generate missing Date

Hi Vinod

Dynamic means : Like 1 to 100