topic Re: How to generate missing dates in QlikView

How to generate missing dates

trdandamudi — Wed, 02 Mar 2016 17:25:38 GMT

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

Re: How to generate missing dates

sunny_talwar — Wed, 02 Mar 2016 18:33:57 GMT

Did you forget to attach TestMissingDates.xlsx?

Re: How to generate missing dates

trdandamudi — Wed, 02 Mar 2016 18:56:04 GMT

Sunny,

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

Thanks

Re: How to generate missing dates

maxgro — Wed, 02 Mar 2016 19:34:34 GMT

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;

Re: How to generate missing dates

trdandamudi — Wed, 02 Mar 2016 19:47:31 GMT

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

Re: How to generate missing dates

sunny_talwar — Wed, 02 Mar 2016 20:35:33 GMT

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?

Re: How to generate missing dates

sandeep_sarawgi — Wed, 02 Mar 2016 20:42:19 GMT

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;

Re: How to generate missing dates

maxgro — Wed, 02 Mar 2016 20:49:28 GMT

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;

Re: How to generate missing dates

trdandamudi — Wed, 02 Mar 2016 20:58:58 GMT

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...

Re: How to generate missing dates

trdandamudi — Thu, 03 Mar 2016 01:33:29 GMT

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

Re: How to generate missing dates

trdandamudi — Thu, 03 Mar 2016 02:34:11 GMT

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

Re: How to generate missing dates

trdandamudi — Thu, 03 Mar 2016 15:16:10 GMT

Maxgro,

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

Thanks

Re: How to generate missing dates

sandeep_sarawgi — Thu, 03 Mar 2016 17:30:09 GMT

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

Re: How to generate missing dates

maxgro — Thu, 03 Mar 2016 20:04:43 GMT

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;

Re: How to generate missing dates

trdandamudi — Fri, 04 Mar 2016 16:43:19 GMT

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...

Re: How to generate missing dates

trdandamudi — Fri, 04 Mar 2016 16:47:27 GMT

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.