Find if date period includes Saturday

[#1]

Hello QV Experts,

I have requirement to set "Saturday indicator" flag which derived based on if there is any saturday between Start Date and End Date.

Journey Start Date=04/29/2012

Journey End Date=05/08/2012

If dates are as above, since 05/05/2012 is Saturday, for record "Saturday indicator" will be set to "Y".

Journey Start Date=04/29/2012

Journey End Date=05/03/2012

Since there is no Saturday during above date period, "Saturday indicator" will be set to "N".

How to achieve this in QV scripting?

Thanks in advanced for your help.

[#2]

Hi,

You can achive that by using the weakday of startDate and the interval between EndDate-StartDate.

Its Look like this when [Order Days Gap] is EndDate-StartDate  and i calculate the # of Saturday:

Floor(if([Order Days Gap]>0,

                         if(startDate ='Mon',([Order Days Gap]+1)/7,

                                        if(startDate ='Tus',([Order Days Gap]+2)/7,

                                                       if(startDate ='Wen',([Order Days Gap]+3)/7 ,

                                                                      if(startDate ='Thur',([Order Days Gap]+4)/7 ,

                                                                                     if(startDate ='Fri',([Order Days Gap]+5)/7 ,[Order Days Gap]/7)))))

if you need only y/n, you can add if Statment befor,

if(

Floor(if([Order Days Gap]>0,

               if(startDate ='Mon',([Order Days Gap]+1)/7,

                        if(startDate ='Tus',([Order Days Gap]+2)/7,

                                   if(startDate ='Wen',([Order Days Gap]+3)/7 ,

                                            if(startDate ='Thur',([Order Days Gap]+4)/7 ,

                                                       if(startDate ='Fri',([Order Days Gap]+5)/7 ,[Order Days Gap]/7)))))

                    >0 , Y, N)

Hope it Help.

[#3]

Hi Ketan,

You can use the below script as an alternative of what Yigal has told.

//Generate table contain All saturdays date

AllSaturdays:

load Timestamp(Date('01/07/2012','MM/dd/YYYY')+((RecNo()-1)*7),'MM/dd/YYYY') as date, RecNo() as Sno, 'Y' AS Indicator AutoGenerate(52);

//Actual Data

Data:

LOAD * INLINE [

    Sno, StartDate, EndDate

    1, 04/29/2012, 05/08/2012

    2, 04/29/2012, 05/03/2012

    3, 05/01/2012, 05/04/2012

    4, 05/01/2012, 05/05/2012

];

//Using interval match to find that start date, end date contains any saturday or not

left join IntervalMatch(date) load StartDate, EndDate Resident Data;

left join load date, Indicator Resident AllSaturdays;

//dropping unwanted fields

drop Field date;

DROP Table AllSaturdays;

Hope this help,

Anosh

[#4]

Thanks Anosh & Yigal for your prompt reply.

I was also trying by myself while I post this reply and I come up with below solution which seems like working.

I am deriving first Saturday date after my Journey start date, and if it is greater than by Journey End date, it is very clear that my Saturday flag is "N". If my derived date is less than my Journey End date, it also obvious that I have Saturday during travel period.

if(date([Journey Start Date]+if((5-num(WeekDay([Journey Start Date])))=-1,6,(5-num(WeekDay([Journey Start Date])))))>[Journey End Date],'N','Y') as“Saturday Indicator”

Thanks again for your help. Both of your approach help me to think logic in some different way.

Thanks

Ketan