Distinct Month Begin and End

[tchovanec]

I am trying to create a table that has distinct Month begin and end dates. Below is what I am trying to do. The code below loads 1524 rows into the MonthEnd table. I am trying to have it load only 51 rows since that is how many month start and end dates there have been since 2009. Any help would be appreciated. Thank you.

LET vMinDate = NUM(DATE('1/1/2009'));
 LET vMaxDate = NUM(DATE(TODAY()));
 TempCalendar:
 LOAD
      $(vMinDate) + RowNo() - 1 AS DateNumber
 AUTOGENERATE 1
 WHILE $(vMinDate) + IterNo()-1 <= $(vMaxDate);
 MonthEnd:
 Load
       Floor(MonthEnd(DateNumber))as MonthEnd
      ,Floor(MonthStart(DateNumber)) as MonthStart
 RESIDENT TempCalendar;
 

[MayilVahanan]

Hi

Try like this

MonthEnd:

Load

       MonthEnd(Floor(DateNumber))as MonthEnd

      ,MonthStart(Floor(DateNumber)) as MonthStart

RESIDENT TempCalendar;

Hope it helps

[tchovanec]

I have tried that it it still loads 1,524 rows. I am not sure why this is happening. When this table is loaded into a straight table it only shows 51 rows. I need it to load 51 rows because I OUTER JOIN to this table and it makes a difference of over 200 million rows.

[MayilVahanan]

HI

MonthEnd:

Load Distinct *;

Load

       MonthEnd(Floor(DateNumber))as MonthEnd

      ,MonthStart(Floor(DateNumber)) as MonthStart

RESIDENT TempCalendar;

Its load 1524 rows ,because it convert each and every date into month end and monthstart, so try like above.

[tchovanec]

It is still fetching 1524 rows as seen below.  

TempCalendar << AUTOGENERATE(1) 1,524 lines fetched
MonthEnd << TempCalendar 1,524 lines fetched

[MayilVahanan]

Hi

Please check the attached file

[tchovanec]

When I execute the file it still shows that 1524 lines have been fetched. Is this correct?

[tchovanec]

As you can see from above it is fetching 1524 lines still. It does only show 51 in a table box or straight table. But the issue is I am outer joining to this table and I need to join to 51 rows and not 1524 because it causes the application to take much longer to run.

[MayilVahanan]

Hi

Its correct.. Because, one month contain 30/31 days.. but monthstart(ex:01/01/2013) and month end(01/31/2013) .. its mapp with all days. Its logic behind it.

date monthstart monthend

01/01/2013 01/01/2013 01/31/2013

01/02/2013 01/01/2013 01/31/2013

01/03/2013 01/01/2013 01/31/2013

....

01/31/2013 01/01/2013 01/31/2013

So for each month , each day map with month end and month start.

Edit:

If you see in table viewer, you can notice that only 51 rows in the monthend table.

[tchovanec · Accepted solution]

Thanks for all the insight and help. Here is how I got it to work.

LET vMinDate = NUM(DATE('1/1/2009'));
LET vMaxDate = NUM(DATE(TODAY()));
 TempCalendar:
 LOAD
      $(vMinDate) + RowNo() - 1 AS DateNumber
 AUTOGENERATE 1
 WHILE $(vMinDate) + IterNo()-1 <= $(vMaxDate);
 MonthEnd:
 Load
       Floor(MonthEnd(DateNumber))as EndMonth
      ,Floor(MonthStart(DateNumber)) as StartMonth
 RESIDENT TempCalendar;

 Max_MonthEnd:
 Load
      MonthEnd(EndMonth) as MnthEnd,
      MonthStart(StartMonth) as MnthStrt,
      MAX(EndMonth) as Max_MonthEnd
RESIDENT MonthEnd
Group By EndMonth, StartMonth;