How to generate missing dates

[trdandamudi]

Hi,

I am working on a application where I need to generate the missing dates for the process. I did searched the forum and able to find some answers but not exactly. I was trying to use Henry's code to replicate what I need but no luck.

Here is the code I am using:

TempTable_Rates:

LOAD Date,

     Rate

FROM

(ooxml, embedded labels, table is Sheet1);

     MinMaxDate:

     Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

     Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

     Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

     Join (TempTable_Rates)

     Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

     Rates:

     NoConcatenate Load Date,

          If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate

          Resident TempTable_Rates

          Order By Date ; /* so that above values can be propagated downwards */

     Drop Table MinMaxDate, TempTable_Rates;

The source data is (TestMissingDates.xlsx) as below: 

Date	Rate
1/1/2016	105450
1/2/2016	105450
1/3/2016	105450
1/4/2016	105450
1/5/2016	105450
1/6/2016	105450
1/7/2016	105450
1/8/2016	105450
1/9/2016	105450
1/10/2016	105450
1/11/2016	105450
1/12/2016	105450
1/13/2016	105450
1/14/2016	105450
1/15/2016	105450
1/16/2016	105450
1/17/2016	105450
1/18/2016	105450
1/19/2016	105450
1/20/2016	105450
1/21/2016	105450
1/22/2016	105450
1/23/2016	105450
1/31/2016	105450
2/17/2016	105450
2/18/2016	105450
2/19/2016	105450
2/20/2016	105450
2/22/2016	105450
2/29/2016	105450

Result:

I need records from 01/01/2016 To 2/29/2016 along with the Rate column. When I am running the code i am getting records for all january but February I am getting only from 2/17/2016 to 2/29/2016.

I missing something here and appreciate if someone can take a look and let know.

Thanks

[trdandamudi]

Sandeep,

Thanks for taking a look on this. I did tried your code and I still have one issue. For the Rate 215914 the date is starting from 2/15/2016 to 2/29/2016. It should be from 2/1/2016 to 2/29/2016. I am attaching the qlikview file here...

Thanks

[trdandamudi]

Maxgro,

Yesterday i did sent out an qlikview file explaining the issue but for some reason it is showing as "Currently being moderated..."

Thanks

[sandeep_sarawgi]

Greetings Thirumala,

I just looked at you QVW file.

I think the code is working fine. 

If you instead needed only 215914--it seems like the values for Feb 5th are not correctly listed in your load script.

Please let me know what you find and I hope this helps.

1/10/2016 215914 0

1/10/2016 215914 1

2/15/2016 215914 2 

2/5/2016 218450 6  *****

2/5/2016 218450 7  *****

3/1/2016 220481 8

[maxgro · Accepted solution]

TestMissingDates:

load * inline [

Date Rate Grade

1/10/2016 215914 0

1/10/2016 215914 1

2/15/2016 215914 2

2/5/2016 218450 6

2/5/2016 218450 7

3/1/2016 220481 8

] (delimiter is spaces);

TempTable_Rates:

NoConcatenate

LOAD Date,

     Rate,

     Grade

resident TestMissingDates;

MinMaxDate:

Load Min(MonthStart(Date)) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

Join (TempTable_Rates)

Load

  Date(recno()+$(vMinDate)) as Date,

  MonthStart(recno()+$(vMinDate)) as FirstOfMonth

Autogenerate vMaxDate - vMinDate;

Left Join (TempTable_Rates)

Load

  FirstOfMonth,

  Subfield(Concat(Rate, '@', Grade), '@', 1) as NewRate

Resident TempTable_Rates

group by FirstOfMonth;

Drop Table TestMissingDates, MinMaxDate;

DROP Field Rate;

RENAME Field NewRate to Rate;

[trdandamudi]

It is looking good for Rate = 215914. But there are no records for Rate = 218450 ?

To just be more specific I think the logic can be as below:

1) In the Script you can have a Start_Date and End_Date  <--- I will hard code the date before running the script for the months required. (MM/DD/YYYY)

2) Now every "Rate" should have a record from Start_Date to End_Date.(It should only insert the missing dates)

Hope I am clear and thanks for your time...

[trdandamudi]

Sandeep,

To just be more specific I think the logic can be as below:

1) In the Script you can have a Start_Date and End_Date  <--- I will hard code the date before running the script for the months required. (MM/DD/YYYY)

2) Now every "Rate" should have a record from Start_Date to End_Date.(It should only insert the missing dates)

In this way I can change my Start_Date and End_Date as per requirement and run the process.

Hope this is clear and Thanks for your time.