Fill in missing dates till today

[mariia_gridneva]

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.

[Peter_Cammaert]

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.

[maxgro · Accepted solution]

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;

[mariia_gridneva]

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.

[khanashique]

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.

[Brett_Bleess]

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