Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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.
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.
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