topic How to Generate missing Date in QlikView

How to Generate missing Date

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

Hi All.

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

Please any one give me solution.

Regards,

Vinod.

Re: How to Generate missing Date

tush — Wed, 19 Sep 2018 04:54:02 GMT

Hi Vinod,

Please reffer below link surely it will help,

Generate missing Dates

Thanks:-

Tushar

Re: How to Generate missing Date

tresB — Wed, 19 Sep 2018 04:55:10 GMT

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

Re: How to Generate missing Date

pooja_prabhu_n — Wed, 19 Sep 2018 05:47:59 GMT

Hi,

Please find the attached qvw file

Thanks,

Pooja

Re: How to Generate missing Date

Anonymous — Wed, 19 Sep 2018 06:51:32 GMT

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

Re: How to Generate missing Date

Vinod — Wed, 19 Sep 2018 06:59:52 GMT

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

Re: How to Generate missing Date

Vinod — Wed, 19 Sep 2018 07:00:24 GMT

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

Re: How to Generate missing Date

Anonymous — Wed, 19 Sep 2018 07:10:35 GMT

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

Re: How to Generate missing Date

Vinod — Wed, 19 Sep 2018 07:23:30 GMT

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

Re: How to Generate missing Date

Anonymous — Wed, 19 Sep 2018 07:48:39 GMT

Hi Vinod

Dynamic means : Like 1 to 100

Re: How to Generate missing Date

pooja_prabhu_n — Wed, 19 Sep 2018 09:33:13 GMT

Hi,

Try this

if(Len(Trim(ID))=0, Peek(ID),ID) as ID,

If you want just Random number use Rand() function like below

if(Len(Trim(ID))=0, Rand(),ID) as ID,

Please find the updated qvw file

Thanks,

Pooja

Re: How to Generate missing Date

Vinod — Wed, 19 Sep 2018 11:06:20 GMT

Thank You, It's working fine for the sample data shared. But when i used with my actual data I got result as in the attached file.

Re: How to Generate missing Date

pooja_prabhu_n — Wed, 19 Sep 2018 12:59:17 GMT

Hi,

I tried with Data.xls for that it is working fine.

Can you share some sample data scenarios for which you are facing issue.

Re: How to Generate missing Date

Vinod — Thu, 20 Sep 2018 07:22:07 GMT

Hi.Can you please send me the qvw file.

Re: How to Generate missing Date

pooja_prabhu_n — Thu, 20 Sep 2018 07:34:10 GMT

PFA

Re: How to Generate missing Date

Vinod — Thu, 20 Sep 2018 09:37:38 GMT

Thank You.