topic Re: Generate weekend dates between two dates in QlikView

Generate weekend dates between two dates

chaitanyajami — Wed, 03 Mar 2021 06:17:45 GMT

Hi All,

i have a change request data for each change number has a Start date and End date . iam actually trying to generate the weekend dates between start and end date for each change number where the weekend should be less than end date, im using below script , but its not working, please suggest me how to achieve the same.

GCIO_All_Dates_Temp:
LOAD [Change Number],
Date([Scheduled Start (UTC)]+iterno()-1) as Date

resident GCIO while [Scheduled Start (UTC)]+IterNo()-1 <=[Scheduled End (UTC)];

left join (GCIO_All_Dates_Temp)

Max_Date:
Load [Change Number],
Date(Max(Date)) as Max_Date
Resident GCIO_All_Dates_Temp group by [Change Number] ;

GCIO_All_Dates:
LOAD [Change Number],
Date,
WeekEnd(Date,0,0) as GCIO_Weekend_Date
Resident GCIO_All_Dates_Temp while WeekEnd(Date,0,0) < Max_Date ;

Drop Table GCIO_All_Dates_Temp;

Re: Generate weekend dates between two dates

edwin — Wed, 03 Mar 2021 20:04:29 GMT

tweak this to your requirements and see if it will work:

NoConcatenate load * where match(Day,'Sat','Sun')>0; load date(start+iterno()-1) as Dt, weekday(date(start+iterno()-1)) as Day while date(start+iterno()-1) < end ; load * inline [ start, end 5/1/2020, 10/4/2020 ]

Re: Generate weekend dates between two dates

edwin — Wed, 03 Mar 2021 20:05:01 GMT

it will only give you weekends