topic Re: Fill in missing dates till today in QlikView

Fill in missing dates till today

mariia_gridneva — Fri, 08 Jan 2016 10:42:21 GMT

Hello dear Qlik developers!

I have trouble with generating and filling in missing dates in the table from startdate untill today.

What I have in this example is 4 dates with start 2015-03-16 and price values which changes with those four dates.

Simply, the source contains article numbers, log date and price that changes on those dates.

What I need is automatically generated dates for all days from min(date) to today()'s date. And also peek:ed price values.

A was looking around in Community for 2 days and tried many scripts. And none of them really worked, unfortunately.

Thanks in advance.

Re: Fill in missing dates till today

Peter_Cammaert — Fri, 08 Jan 2016 17:28:19 GMT

Is there a general starting date (for example the earliest date for all products) or should the peeking just start from the earliest day for the current product?

Script solutions usually JOIN a calendar to all products, and then start to fill in the blanks. Problem with this approach is that some dates may be before the first line for some products.

It would certainly help creating a solution if you could post a more representative sample of your data.

Re: Fill in missing dates till today

maxgro — Fri, 08 Jan 2016 21:19:43 GMT

added some rows to check with more than one artno

some help here How to populate a sparsely populated field

SOURCE:

load * inline [

ArtNo, LogDate, Price

57, 16/08/2015, 3.02

57, 13/09/2015, 3.8

57, 01/10/2015, 2.97

57, 23/11/2015, 3.15

5, 01/08/2015, 3.02

5, 03/08/2015, 3.8

5, 01/09/2015, 2.97

5, 03/09/2015, 3.15

];

TempTable_Rates:

NoConcatenate Load ArtNo, LogDate, Price Resident SOURCE ;

MinMaxDate:

Load ArtNo, Min(LogDate) as MinDate, Max(LogDate) as MaxDate resident TempTable_Rates

Group By ArtNo;

Join (TempTable_Rates)

Load

ArtNo,

Date(iterno()+MinDate) as LogDate

Resident MinMaxDate

While iterno()+MinDate <= MaxDate;

Rates:

NoConcatenate Load ArtNo, LogDate,

If( IsNull( Price ), Peek( Price ), Price ) as Price

Resident TempTable_Rates

Order By ArtNo, LogDate ;

Drop Table MinMaxDate, TempTable_Rates;

Re: Fill in missing dates till today

mariia_gridneva — Sat, 09 Jan 2016 10:25:11 GMT

Max,

You modified Cronström's script perfectly. I was "fighting" with his sample for a whole day and it did not work properly.

I am going to use your script, it would work flawless.

The only thing I changed was max(logdate) to today().

Thank you a lot for help.

Re: Fill in missing dates till today

khanashique — Thu, 14 Mar 2019 08:44:31 GMT

Hi There,

I have similar case, where i want to show missing dates for any year or month selected.

for example if i select year 2019 and month april the missing dates should show with the values as 0 where there is no value...

Thanks.

Re: Fill in missing dates till today

Brett_Bleess — Mon, 25 Mar 2019 19:48:33 GMT

See the following Design Blog post regarding Master Calendar done by Henric Cronstrom, believe this is for what you are looking:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Regards,
Brett