topic Re: get a date excluding the weekends in load script in QlikView

get a date excluding the weekends in load script

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team ,

I have a date1 field . i want to create an another field date2 which will be the date1 +3 days but should exclude weekends like saturday and sunday

kaushiknsolanki — Tue, 30 Aug 2016 07:33:27 GMT

Hi,

Do you mean the base date(Date1) should not be the Saturday Sunday?

If yes then you can do this

If(not Wildmatch(Weekday(Date1),'Sat','Sun'),Date(Date1 + 3)) as Date2

If you want Date2 to exclude the Saturday and Sunday, try this

If(not Wildmatch(Weekday(Date(Date1+3)),'Sat','Sun'),Date(Date1 + 3)) as Date2

Regards,

Kaushik Solanki

Anonymous — Tue, 30 Aug 2016 07:34:24 GMT

may be like this?

Load

Date1,

IF(weekday(Date1)<>'Sat' or weekday(Date1)<>'Sun', num(Date1)+3) as Date2

from Table;

Tue, 30 Aug 2016 07:35:23 GMT

Hi Bionod,

assuming your date is a date number (integer like 43255) and fieldname is DayNr (check with klistbox and change format to Integer)

load * where Day <> 'Sa' and Day <> 'Su'; //Deselect the Saturdays and Sundays you can do this also with daynumer (of the week)

Weekday(NewDayNr) as Day

load *,

DayNr + 3 as NewDayNr

resident YourTable;

Anonymous — Tue, 30 Aug 2016 07:36:05 GMT

Hi Binod,

You can calculate de weekday, if it is on weekend you can add more days, something like this:

Date+3+IF(WeekDay(Date+3) = 0,1,IF(WeekDay(Date+3) = 6,2,0))

Regards!!

Anonymous — Tue, 30 Aug 2016 07:40:10 GMT

try lastworkdate which doesnot calculate Weekends

lastworkdate(date1,3) as date2

if date1+3 is a Weekend, the next monday will be defined

Tue, 30 Aug 2016 07:49:18 GMT

yes my base date will always be date1