topic Re: Between Date Range in QlikView

Between Date Range

Wed, 20 Feb 2013 08:16:44 GMT

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.

Re: Between Date Range

Wed, 20 Feb 2013 08:22:45 GMT

Can you upload sample data.

Re: Between Date Range

Wed, 20 Feb 2013 08:33:02 GMT

Hi Altafamber,

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

Thanks,

Selva

Re: Between Date Range

Wed, 20 Feb 2013 08:58:45 GMT

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

Re: Between Date Range

Wed, 20 Feb 2013 10:21:12 GMT

Thanks Jp. It's Working..