Populate sequential dates from a date range_performance issue

[joydipp1988]

Hi all,

Warm wishes for New Year, 2017. Hope everyone doing well. Now lets see the problem.

I've following data-

I have to generate sequential dates between StartDate and EndDate. Let me explain it briefly.

E.g. we pick the first record i.e. Id=1. Here StartDate = 10-Apr-2016 and EndDate = 25-Oct-2016.

I want to generate one record under date field for each month between the range and the date will be the last date of the month.

i.e. for Id=1 the output will look like below-

One special case is for some rows EndDate is NULL, e.g. Id=4. In this case we need to consider today's date as EndDate.

I build one solution using For-Next loop and got the desired output here. My code is given below-

INPUT:

LOAD

  Id,

  StartDate,

  EndDate

FROM

New_Data.xlsx

(ooxml, embedded labels, table is Input);

FOR i=0 to NoOfRows('INPUT') - 1

  LET vId           = Peek('Id', $(i), 'INPUT');

  LET vStartDate    = Num(Peek('StartDate', $(i), 'INPUT'));

  LET vTempEndDate  = Num(Peek('EndDate', $(i), 'INPUT'));

  LET vEndDate      = If(IsNull(vTempEndDate), Num(Date(Today())), $(vTempEndDate));

  OUTPUT:

  LOAD Distinct

  *,

  MonthName(SequentialDate) as Month_Year;

  LOAD Distinct

  $(vId)                                       as NewID,

  Date(MonthEnd($(vStartDate) + RecNo() -1 ))  as SequentialDate

  AutoGenerate $(vEndDate) - $(vStartDate) +1;

NEXT i

The Problem:

I got the desired output for this dummy data, where data size is very small. But in real time I've 55000 records. So when I'm applying the same method, the For loop is running 55000 times and hence taking more than 45 minutes to complete reload. (My machine configuration is- 16 GB RAM and intel i7 processor). Please guide how to achieve the solution within less time.

The data and .qvw file are given as attachment. In the excel file, data under Input tab is the input data and data under Output tab is the desired output data. Please find the attachment.

Thanks in advance,

Joy

[sunny_talwar · Accepted solution]

Don't know if this will help with performance, but you can try with While Loop

INPUT:

LOAD Id,

  Date(MonthEnd(StartDate, IterNo()-1)) as SequentialDate,

  MonthName(MonthEnd(StartDate, IterNo()-1)) as Month_Year

While MonthStart(StartDate, IterNo()-1) <= MonthStart(If(Len(Trim(EndDate)) > 0, EndDate, Today()));

LOAD Id,

  StartDate,

  EndDate

FROM

New_Data.xlsx

(ooxml, embedded labels, table is Input);

[johnw]

I bet it will help a lot with performance, though I guess I've technically never tried to compare the two, just always assumed a while loop would be dramatically faster.

[settu_periasamy]

Used stalwar1‌ Script. Just added 'NoOfMonths'

INPUT:
LOAD        Id as NewID,
          StartDate,EndDate,
          Date(MonthEnd(StartDate, IterNo()-1)) as SequentialDate,
          MonthName(MonthEnd(StartDate, IterNo()-1)) as Month_Year
While IterNo()-1 <= NoOfMonths;

LOAD    Id,
          StartDate,
          EndDate,
          ((year(Alt(EndDate,Today()))*12)+month(Alt(EndDate,Today()))) -          (((year(StartDate)*12)+month(StartDate))) as NoOfMonths
  FROM
New_Data.xlsx
(ooxml, embedded labels, table is Input) ;

NoOfMonths Calculation : How Do I Get the Number of Months Between Two Dates?

[MarcoWedel]

Hi,

another solution (quite similar to the already proposed ones) might be:

INPUT:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1187133-259622/New_Data.xlsx] (ooxml, embedded labels, table is Input);

OUTPUT:
LOAD *,
    DayName(MonthEnd(Month_Year)) as SequentialDate;
LOAD Id,
    MonthName(StartDate,IterNo()-1) as Month_Year
Resident INPUT
While MonthName(StartDate,IterNo()-1) <= Alt(EndDate,Today());

Instead of your Date(MonthEnd()) expression I used DayName(MonthEnd()) because Date() only formats the MonthEnd() value, i.e. although the result looks like a regular date it is not, it still contains a fractional time part. So your SequentialDate field would not link correctly to a calendar you might use in your application.

hope this helps

regards

Marco

[joydipp1988]

Hello stalwar1‌,

I applied your code and it worked like a magic. I never knew that While loop works so fast. Where the For-Next was taking near about 30-35 minutes, your code took only few seconds. Nice to hear from you and this is truly amazing to be in touch with you all.

Thanks,

Joy

[sunny_talwar]

I am glad it worked, but one thing to pay attention (as pointed out by marcowedel‌) is that MonthEnd() will give your a timestamp so it would be nice to either use DayName() function as he proposed or Use Date(Floor()) function to make sure that your SequentialDate is only date and doesn't include any time component if you don't really need it

INPUT:

LOAD Id,

  Date(Floor(MonthEnd(StartDate, IterNo()-1))) as SequentialDate,

  MonthName(MonthEnd(StartDate, IterNo()-1)) as Month_Year

While MonthStart(StartDate, IterNo()-1) <= MonthStart(If(Len(Trim(EndDate)) > 0, EndDate, Today()));

LOAD Id,

  StartDate,

  EndDate

FROM

New_Data.xlsx

(ooxml, embedded labels, table is Input);

or

INPUT:

LOAD Id,

  DayName(MonthEnd(StartDate, IterNo()-1)) as SequentialDate,

  MonthName(MonthEnd(StartDate, IterNo()-1)) as Month_Year

While MonthStart(StartDate, IterNo()-1) <= MonthStart(If(Len(Trim(EndDate)) > 0, EndDate, Today()));

LOAD Id,

  StartDate,

  EndDate

FROM

New_Data.xlsx

(ooxml, embedded labels, table is Input);