Between Date Range

[#1]

Hi,

I want to create table with 3 Column (From Date,To Date and Sales Date) like this

Now when i Choose the From Date (1/5/2012) and To Date (1/10/2012) then Sales date should list only from ( 1/5/2012 to 1/10/2012) some thing like this

/---------------------------------------------------------------------
Let StartCalendar = num(MakeDate(year(today())-1, '01', '01'));
//Let EndCalendar = num(YearEnd(today()));
//---------------------------------------------------------------------

//Autogenerate Calendar with start and end Date

DATE_RANGE_0:
LOAD Date ($(StartCalendar) + RecNo()-1) as FromDate
AutoGenerate ((today()-1) - $(StartCalendar)+1 );

//Left Join with no keys
Left Join (DATE_RANGE_0)
LOAD FromDate as ToDate
Resident DATE_RANGE_0;

NoConcatenate
DATE_RANGE_1:
LOAD * Resident DATE_RANGE_0 where ToDate >= FromDate;

drop Table DATE_RANGE_0;

NoConcatenate
DATE_RANGE_2:
Load FromDate as SALES_DATE Resident DATE_RANGE_1 ;

NoConcatenate
DATE_RANGE_3:
LOAD * Resident DATE_RANGE_2 where SALES_DATE >= FromDate and SALES_DATE <= ToDate;

But error is coming.

Please find the sample qvw attached.

[#2]

Hi

Can you upload sample data.

[#3]

Hi Altafamber,

It's an autogenerated date. Please find the attached QVW above.

Thanks,

Selva

[#4 · Accepted solution]

Hi Selva,

You can use the code below:

//---------------------------------------------------------------------

Let StartCalendar = num(MakeDate(year(today())-1, '01', '01'));

//Let EndCalendar = num(YearEnd(today()));
//---------------------------------------------------------------------



//Autogenerate Calendar with start and end Date



DATE_RANGE_0:

LOAD Date($(StartCalendar) + RecNo()-1) as SalesDate

AutoGenerate ((today()-1) - $(StartCalendar)+1 );



DATE_RANGE_1:

LOAD SalesDate as FromDate

Resident DATE_RANGE_0;



Left Join(DATE_RANGE_1)

LOAD SalesDate as ToDate

Resident DATE_RANGE_0;



NoConcatenate

DATE_RANGE_2:

LOAD * Resident DATE_RANGE_1 where ToDate >= FromDate;



drop Table DATE_RANGE_1;



IntervalMatch(SalesDate) LOAD FromDate, ToDate Resident DATE_RANGE_2;

DROP Table DATE_RANGE_2;

DROP Table DATE_RANGE_0;

Hope this helps

[#5]

Thanks Jp. It's Working..