topic Re: Distinct Month Begin and End in QlikView

Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 20:33:13 GMT

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;

Re: Distinct Month Begin and End

MayilVahanan — Mon, 04 Mar 2013 20:38:29 GMT

Try like this

MonthEnd:

Load

MonthEnd(Floor(DateNumber))as MonthEnd

,MonthStart(Floor(DateNumber)) as MonthStart

RESIDENT TempCalendar;

Hope it helps

Re: Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 20:45:08 GMT

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.

Re: Distinct Month Begin and End

MayilVahanan — Mon, 04 Mar 2013 20:50:05 GMT

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.

Re: Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 20:55:35 GMT

It is still fetching 1524 rows as seen below.

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

Re: Distinct Month Begin and End

MayilVahanan — Mon, 04 Mar 2013 21:01:38 GMT

Please check the attached file

Re: Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 21:10:55 GMT

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

Re: Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 21:15:46 GMT

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.

Re: Distinct Month Begin and End

MayilVahanan — Mon, 04 Mar 2013 21:17:15 GMT

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.

Re: Distinct Month Begin and End

tchovanec — Mon, 04 Mar 2013 21:25:51 GMT

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;