Exclude weekends in for loop load script

[ranganadh1979]

Hi All,

May i know how to exclude the weekends in my for loop load script in qlikview?

I have 2 date variables. start date and end date. Start date is static. I am loading the data per day by using for loop and storing in a qvd based on end date variable. 

i want to load only work days(monday to friday) not weekends(Saturday and Sunday) in my for loop.

Any suggestions??

[sunny_talwar]

Can you share the script that you currently have?

[ranganadh1979]

Hi Sunny,

Thanks for your reply. please find the below script.

let vDatefrom=Floor('2019-09-30');//Floor(yearstart(Today()));
let vDateTo=Floor(Today());

let vApiStartDate= date($(vDatefrom),'DD%20MMMM%20YYYY');

for i=$(vDatefrom) to $(vDateTo)

let vApiEndDate= date($(i),'DD%20MMMM%20YYYY');

Fact_Table:
LOAD *

FROM
[http://***/***/Report?api-key=***&reportname=Complete%20TB%20Report&headers=TRUE&arg1=$(vApiStartDate)&arg2=$(vApiEndDate)&response-type=csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate
LOAD *

FROM
[C:\UsersQVD\Full_Load.qvd](qvd);

store Fact_Table into C:\UsersQVD\Full_Load.qvd (qvd);

drop table Fact_Table;

next i;

I dont want to load all dates in the for loop. i want to load only week days data and append into qvd.

Thanks in advance

[sunny_talwar · Accepted solution]

Try this

LET vDatefrom = Num(MakeDate(2019, 9, 30));//Floor(yearstart(Today()));
LET vDateTo = Num(Today());

LET vApiStartDate = Date($(vDatefrom),'DD%20MMMM%20YYYY');

FOR i = $(vDatefrom) to $(vDateTo)

	LET vApiEndDate = Date($(i),'DD%20MMMM%20YYYY');
	LET vDay = WeekDay($(i));
	If '$(vDay)' <> 'Sun' and '$(vDay)' <> 'Sat' THEN
	
		LOAD '$(vApiEndDate)' as ApiEndDate,
			 '$(vDay)' as WeekDay
		AutoGenerate 1;
	
	ENDIF
	
NEXT

[ranganadh1979]

Thanks Sunny,

It's working...

One more thing.. how to exclude public holidays in that?

Thanks in advance

[sunny_talwar]

Each country have it's own public holiday. and sometimes the list changes based on what you observe... you need to supply the list and then it can be done

[ranganadh1979]

Thanks Sunny,

i sorted out..