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

[sunny_talwar]

Did you forget to attach TestMissingDates.xlsx?

[trdandamudi]

Sunny,

No, The source data has only two columns which is Date and Rate and the data is listed out.

Thanks

[maxgro]

With your code I got all the dates; in image an exttract of the result dates after running the code, you can see february, starting from 1st

SET DateFormat='M/D/YYYY';

TestMissingDates:

load * inline [

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

] (delimiter is spaces);

TempTable_Rates:

NoConcatenate

LOAD Date,

    Rate

resident TestMissingDates;

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

trace $(vMinDate) $(vMaxDate);

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;

[trdandamudi]

Maxgro,

Thanks for the reply... Please disregard the source data and here is the actual data below:

Date	Rate	Grade
1/10/2016	215914	0
1/10/2016	215914	1
1/10/2016	215914	2
2/5/2016	218450	6
2/5/2016	218450	7
3/1/2016	220481	8

if I load the above data the result should be as below:

There should be a date (record) from 1/1/2016 to 1/31/2016 for the Rate 215914

There should be a date (record) from 2/1/2016 to 2/29/2016 for the Rate 218450

There should be a date (record) only for 3/1/2016  for the Rate 220481

Hope this is clear...

Thanks

[sunny_talwar]

Would you be able to explain the logic for the following:

1) Why does 220481 doesn't span the whole month of March?

2) What will be the grade assigned to 215914 for each of the new dates that will be created?

[sandeep_sarawgi]

I tested and this seems to work.  Assuming the name of your Facts table is Data:

MinMonthlyRates:

  NoConcatenate Load

  Date(MonthStart(Min(Date))) as Date,

  FirstValue(Rate) as Rate

   Resident Data

  Group by Year(Date),Month(Date)

  Order by Date;

Join (Data)

Load Date, Rate

Resident MinMonthlyRates;

drop table MinMonthlyRates;

MinMaxDate:

  Load

  MonthStart(Min(Date)) as MinDate,

  Max(Date) as MaxDate

  Resident Data;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

Drop Table MinMaxDate;

Join (Data)

Load Date(recno()+$(vMinDate)-1) as Date

Autogenerate $(vMaxDate) - $(vMinDate) + 1;

Facts:

NoConcatenate Load

Date, Rate, If(isnull(Rate), Peek(RateNew), Rate) as RateNew

Resident Data

Order by Date;

Drop Table Data;

[maxgro]

SET DateFormat='M/D/YYYY';

TestMissingDates:

load * inline [

Date Rate Grade

1/10/2016 215914 0

1/10/2016 215914 1

1/10/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

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;

Rates:

NoConcatenate Load

  Date,

  FirstOfMonth,

  If( IsNull(Rate) and FirstOfMonth=Peek('FirstOfMonth'), Peek('Rate'), Rate ) as Rate

Resident TempTable_Rates

Order By Date ;

Rates2:

NoConcatenate Load

  Date,

  FirstOfMonth,

  If( IsNull(Rate) and FirstOfMonth=Peek('FirstOfMonth'), Peek('Rate'), Rate ) as Rate

Resident Rates

Order By Date desc;

Drop Table Rates, MinMaxDate, TempTable_Rates;

[trdandamudi]

Sunny,

Answers as below:

1) Because it is the last date in the file, It has to stop at the maximum date

2) The grade will be zero.

The bottom line when you click on the 215914 it should show all the dates along with the Grade. The inserted date row will have zero for Grade. So for analysis purpose if I click on 215914 it will show all the days with grade. If i see a zero against a date that means there is no grade for that day.

Hope It is clear now...

[trdandamudi]

Maxgro,

Thank you very much for your time while assisting me on this... I had done couple of tests with your code and one of the scenario is failing.

I am attaching the qlikview file for your reference. The Rate 215914 should have the rows from 1/1/2016 to 1/31/2016 and

from 2/1/2016 to 2/29/2016. Instead for February it shows only from 2/15/2016 to 2/29/2016. Please let me know if you need any further information.

Thank you