Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generate Missing Data between Two Dates

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

MetricIDFromToReturns
101/01/201301/03/20131
101/04/201401/06/20142
201/01/201301/03/20133
201/04/201401/07/20144

Tring to get to this

MetricIDFromToReturns
101/01/201301/01/20131
101/02/201301/02/20131
101/03/201301/03/20131
101/04/201301/04/20132
101/05/201301/05/20132
101/06/201301/06/20132
201/01/201301/01/20133
201/02/201301/02/20133
201/03/201301/03/20133
201/04/201301/04/20134
201/05/201301/05/20134
201/06/201301/06/20134
201/07/201301/07/20134

!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

9 Replies
MK_QSL
MVP
MVP

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;

Not applicable
Author

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;

sasiparupudi1
Master III
Master III

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

];

jonathandienst
Partner - Champion III
Partner - Champion III

Also look here: How to populate a sparsely populated field

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tresesco
MVP
MVP

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.

Not applicable
Author

Thnankyou very much this worked perfectly

Not applicable
Author

Thankyou I was working along these lines orignially 🙂

Not applicable
Author

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

tresesco
MVP
MVP

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.