topic Re: Generate Missing Data between Two Dates in QlikView

Generate Missing Data between Two Dates

Thu, 17 Sep 2015 08:28:45 GMT

Hi All

I have a table in the below format from SQLwhich have Start and End dates to dvise of a given Return.

I need to generate a row for each of the Metrics for each Month between the From and To dates - returning the correct Return Value.

I have tried various loops, iterno() while script and just cannot get it to work correctly with the start and end date fields.

I have uploaded a QV for manipulation - Please can you have a look at the attached app and help :-)) Many thanks in advance

Start Table

MetricID	From	To	Returns
1	01/01/2013	01/03/2013	1
1	01/04/2014	01/06/2014	2
2	01/01/2013	01/03/2013	3
2	01/04/2014	01/07/2014	4

Tring to get to this

MetricID	From	To	Returns
1	01/01/2013	01/01/2013	1
1	01/02/2013	01/02/2013	1
1	01/03/2013	01/03/2013	1
1	01/04/2013	01/04/2013	2
1	01/05/2013	01/05/2013	2
1	01/06/2013	01/06/2013	2
2	01/01/2013	01/01/2013	3
2	01/02/2013	01/02/2013	3
2	01/03/2013	01/03/2013	3
2	01/04/2013	01/04/2013	4
2	01/05/2013	01/05/2013	4
2	01/06/2013	01/06/2013	4
2	01/07/2013	01/07/2013	4

Re: Generate Missing Data between Two Dates

MK_QSL — Thu, 17 Sep 2015 08:41:31 GMT

TEST:
LOAD * INLINE 
[
  MetricID, From, To, Returns
  1, 01/01/2013, 01/03/2013, 1
  1, 01/04/2014, 01/06/2014, 2
  2, 01/01/2013, 01/03/2013, 3
  2, 01/04/2014, 01/07/2014, 4
];


NoConcatenate
Final:
LOAD
  MetricID,
  Date(AddMonths(From,IterNo()-1)) as From,
  Date(AddMonths(From,IterNo()-1)) as To,
  Returns
Resident TEST
While AddMonths(From,IterNo()-1) <= To;


Drop Table TEST;

Re: Generate Missing Data between Two Dates

Thu, 17 Sep 2015 08:44:54 GMT

Hi Joanne,

I usually solve this kind of problem with a Master Calendar table. In the Script below you only need to replace YOURTABLE with the name of your previously loaded table and DATE with the Name of the Date field in YOURTABLE. Also replace DATE in the MasterCalendar table below with the same name, so you get a connection between YOURTABLE and MasterCalendar.

You can erase all date formats you do not need for your purpose from the MasterCalendar table.

//calculate Min and Max Date
MinMaxDate:
Load
  Max(DATE) as MaxDate,
    Min(DATE) as MinDate
Resident YOURTABLE;


//Define Vaiables fpr min and max date


Let vMaxDateDate = date(peek('MaxDate',0,'MinMaxDate'));
Let vMinDate = num(peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = num(peek('MaxDate',0,'MinMaxDate'));


Let vToday = $(vMaxDate);


//Drop not used table
Drop Table MinMaxDate;


//generate dates between min and max date
TempCal:
Load
  date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

MasterCalendar:
LOAD
  TempDate AS DATE,
  TempDate AS FullDate,
  Week(TempDate) AS Week,
  Year(TempDate) AS Year,
  Month(TempDate) AS Month,
  Day(TempDate) AS Day,
  Weekday(TempDate) AS WeekDay,
  'Q' & ceil(month(TempDate) / 3) AS Quarter,
  Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
  Week(TempDate)&'-'&Year(TempDate) AS WeekYear, //Not the real WeekYear
  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag, //For Filter Purpose, is the date in the current year and before today
  inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag //For Filter Purpose, is the date in the last year and before today
RESIDENT TempCal
ORDER BY TempDate ASC;


DROP TABLE TempCal;

Re: Generate Missing Data between Two Dates

sasiparupudi1 — Thu, 17 Sep 2015 09:10:24 GMT

DATA:

load MetricID,

Date(AddMonths(From,IterNo()-1)) as From,

Date(AddMonths(From,IterNo()-1)) as To,

Returns

while AddMonths(From,IterNo()-1) <= To;

LOAD MetricID, Date#(From,'DD/MM/YYYY') as From, Date#(To, 'DD/MM/YYYY')as To, Returns INLINE

[MetricID, From, To, Returns

1,01/01/2013,01/03/2013,1

1,01/04/2014,01/06/2014,2

2,01/01/2013,01/03/2013,3

2,01/04/2014,01/07/2014,4

];

Re: Generate Missing Data between Two Dates

jonathandienst — Thu, 17 Sep 2015 09:31:49 GMT

Also look here: How to populate a sparsely populated field

Re: Generate Missing Data between Two Dates

tresB — Thu, 17 Sep 2015 09:42:49 GMT

DATA:
Load MetricID,
Date(From+IterNo()-1) as From,
Date(From+IterNo()-1) as To,
Returns

While From +IterNo()-1<= To;
LOAD * INLINE
[MetricID, From, To, Returns
1, 01/01/2013, 01/03/2013, 1
1, 01/04/2013, 01/06/2013, 2
2, 01/01/2013, 01/03/2013, 3
2, 01/04/2013, 01/07/2013, 4

];

PFA

I guess there is a little typo inyour sample data. Corrected and tried.

Re: Generate Missing Data between Two Dates

Thu, 17 Sep 2015 10:40:54 GMT

Thnankyou very much this worked perfectly

Re: Generate Missing Data between Two Dates

Thu, 17 Sep 2015 10:41:58 GMT

Thankyou I was working along these lines orignially 🙂

Re: Generate Missing Data between Two Dates

Thu, 17 Sep 2015 10:43:48 GMT

Thankyou everyone - Problem has been solved!

Guess I really need to get my head around the iterno() function - so I can correctly understand how to use it and when.

Many Thanks

Re: Generate Missing Data between Two Dates

tresB — Thu, 17 Sep 2015 10:49:19 GMT

be careful, Manish's solution considers your date format as DD/MM/YYYY, while I considered your date fommat as MM/DD/YYYY. Two would generate different dates, though would look similar.